YTD and Date Tables

[stben]

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!

[v-haibl-msft]

@stben

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

[stben]

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.

[MattAllington · Accepted solution]

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.

[stben]

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!