YTD Troubles

[HLEBOEUF]

Hello.

I'm having a complete time dimension that works fine with the regular YTD, MTD .....

But I've created a 2nd date field that holds for every date the date of yesterday in that year.

Some example

10/06/2016   Holds 10/05/2016

10/03/2016   Holds 10/05/2016

10/06/2014   Holds 10/05/2014

Even if the user makes no date selections I can compare a 'honest' YTD over the years to see progress over the years.

When I look in the data this field is correct.

But as soon as I used a TOTALYTD on that field it doesn't work.

Here are my formulas ...

Year_Today = DATE(YEAR(TODAY()-1)-IF('Dim_Dates'[Dat_Date]>TODAY()-1;BLANK();DATEDIFF('Dim_Dates'[Dat_Date];TODAY()-1;YEAR));MONTH(TODAY()-1);DAY(TODAY()-1))

YToday Visitors All = TOTALYTD(SUM('Fact_Sessions'[Sess_Visitors_All]); 'Dim_Dates'[Dat_Date])  (Working)

YTDToday Visitors All = TOTALYTD(SUM('Fact_Sessions'[Sess_Visitors_All]); 'Dim_Dates'[Year_Today])   (Funny)

And these are the results

I supose i'm abusing the idea of YTD but have no idea why ....

[v-yulgu-msft]

Hi, @HLEBOEUF

To use the TOTALYTD function, you should know that it only works using a separate date table with continuous dates. You should create a calendar table mapping to the data table (in your scenario, I guess it's 'Fact_Sessions'). I suppose that you have linked the table 'Fact_Sessions' to calendar table based on the column 'Dim_Dates'[Dat_Date], so the formula for "YToday Visitors All" worked fine. That is why when you used " 'Dim_Dates'[Year_Today]" in TOTALYTD function it didn't work as you expected. In short, the date column we used in YTD must be a specified column which is the relationship between two linked tables.

If you have any question, please feel free to ask. For more advice, please share some sample data and the relationships of between the tables.

Best regards,
Yuliana Gu