# Year to Date column on same card as monthly

Options
Member

I've been wrestling with this for several months and hope the Domo hive mind can help me out :)

My team uses several graphs like the above for our monthly metrics. "Monthly PPM" is a calculation of `col_1`/`col_2` *1000000. "YTD PPM" should be cumulative `col_1` / cumulative `col_2` * 1000000 with the year starting fresh in May. So May Monthly PPM and YTD PPM are the same but June YTD PPM should be larger than the June Monthly PPM and should be (May `col_1` + June `col_1` ) / (May `col_2` + June `col_2` ) * 1000000. July YTD PPM will be (May `col_1` + June `col_1` + July `col_1`) / (May `col_2` + June `col_2` + July `col_2` ) * 1000000 and so on and so forth.

Everything prior to May was driven by a Google sheet and the Domo card was just graphing it, no calculations. I'm trying to move to a more programmatic approach where the data loads into Domo and the calculations are done in Magic ETL to build a final dataset that looks similar to the old Google sheet. However, I'm stumped how to calculate the YTD!

Can anyone help me or am I attempting the impossible and should go back to the Google sheet method?

• Coach
Options

You can utilize case statements and window functions (you'll need to talk with your CSM to turn on windowing functions)

```SUM(SUM(`col_1`)) OVER (PARTITION BY YEAR(`Date` - INTERVAL '5' MONTH) ORDER BY (`Date` - INTERVAL '5' MONTH)
/
SUM(SUM(`col_2`)) OVER (PARTITION BY YEAR(`Date` - INTERVAL '5' MONTH) ORDER BY (`Date` - INTERVAL '5' MONTH)
*
1000000
```

It's subtracting 5 months since May is the start of your year.

A possibly better option is to have a Date dimension table which defines the fiscal year for each date instead of having the logic in a beast mode. You'd then join the date to your date dimension and partition by that field. This way all your date logic is on a single dataset instead of multiple beast modes.

**Did this solve your problem? Accept it as a solution!**
• Coach
Options

just stack your data with a date dimension.

the gist of it is. create a datastet for one row per date in YTD, and in clude a column "Period Type" = YTD. then UNION all thte dates for Period Type = MTD or and maybe another set of Dates where Period Type = Prev Year YTD

Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨‍💻

**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"