Forum Discussion
YTD and Date Tables
Hi!
I am trying to do what I thought would be a fairly simple YTD measure for variance to budget (Actual minus Budget).
My measure is defined as
NWS VTB $ YTD = TOTALYTD( SUM( PPG[Actual] ) - SUM( PPG[Budget] ) , 'Time Map'[FiscalDate] , "06/01/2017" )
TimeMap is my date table, and Fiscal Date is in MM/dd/YYYY. The FY ends on 06/01/2017. Because our business always talks about Fiscal Months in a different format ( June of FY17 is written as FY2017-P01), in my TimeMap for every Fiscal Date there is a corresponding 'Fiscal Month'. Snapshot below.
In my report I want to graph the variance to budget YTD by FiscalMonth (not FiscalDate). Sadly, for each month I just get the monthly variance. Also, if I try to display the variance in a Card with a FiscalMonth slicer, the YTD only works if I sequentially select the months. Changing to FiscalDate doesn't help either.
Example: Month 1 is -10, and Month 2 is +5. Month 2 YTD should be -5. If I select Month 2 alone in the slicer, I get +5. If I select both Months 1 & 2 I get the correct -5.
How can I fix my DAX or model so each month correctly returns the YTD? Thanks so much!
you are using an inbuilt time intelligence function, but your calendar table doesn't follow the required rules for inbuilt time intelligence functions. You will need to write a custom time intelligence function instead. Read my article here
http://exceleratorbi.com.au/power-pivot-calendar-tables/
Note the sample custom time intelligence function towards the end of the article.
4 Replies
- MattAllington
Community Champion
you are using an inbuilt time intelligence function, but your calendar table doesn't follow the required rules for inbuilt time intelligence functions. You will need to write a custom time intelligence function instead. Read my article here
http://exceleratorbi.com.au/power-pivot-calendar-tables/
Note the sample custom time intelligence function towards the end of the article.
Ah thanks for pointing that out!
I created a new table with just a single column with the Fiscal Dates, connected that to my Date Map, and replaced the reference in the YTD formula with the new table. It works out perfectly!
Thanks again!
v-haibl-msftMicrosoft Employee
What is your PPG table like? I tried with PPG and Time Map as below. It seems that your measure can return the right result. These two tables have relationship with the Date key.
(20 + 17 + 88) – (81 + 6 + 89) = -51
Best Regards,
Herbert
Herbert,
The issue was my PPG table only had the Fiscal Month data, not Fiscal Date. I had to create a separate 'Calendar' table with just the Fiscal Dates, add another Connection to my Time Map, and now it works out.
Thanks for the help.
