TotalYTD in a Matrix

[CobbSalad]

Hi y'all,

I am having difficulty getting totalYTD to work in my matrix. I currently have sales data between 2018 to May 3rd 2021. My goal is for totalYTD to forecast for the rest of the year and then I can visually shift through the dates using a date hierarchy.

The DAX I am currently using is as follows:

Year to Date = TOTALYTD(SUM('Sales Data'[Net Sales]),'Sales Data'[Day].[Date])

I issue might be because all my data is in one query rather than seperate queries?

Any help is greatly appreciated as well as any advice for doing this a different way!

Thanks!

[aj1973]

Hi @CobbSalad 

I think your issue is more like using Time intelligence function without a Calendar table. if you have gaps in your 'Sales Data'[Day].[Date]  then don't count on TOTALYTD to outcome an accurate result.

[CobbSalad]

Thank you for the reply!

I created the calendar table, but I am still having the same issue as before where it just repeats the last sales data from May 3rd 2021. I want to be able to drill down through a date hierarchy. In the dax code what would that look like as I am unsure of how to link it to a hierarchy within the calendar table. I have the calendar hierarchy broken down between 'CalendarYear' 'CalendarQuarter' 'CalendarMonth' 'CalendarDate'.

The DAX I am currently using is as follows:

Year to Date = TOTALYTD(SUM('Sales Data'[Net Sales]),'Calendar Table'[CalendarDate])

Thanks again!

[v-kelly-msft · Accepted solution]

Hi  @CobbSalad,

The hierarchy should be in your original table rather than the calendar table,check my sample below:(remember to create a relationship between the 2 tables)

Check my .pbix file attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!