TOTALYTD (and other Time Intelligence) not working as expected

[robofski]

Simpe formula:

Total Sales YTD = TOTALYTD(sum(VW_PowerBI_Analysis[NetSales]),DimCalendar[Date])

DimCalendar has contigious dates joined to Ship Date DMY in VW_PowerBI_Analysis one to many, one way filtering.

Ship Date DMY is a caculated column in the model as the data only has yyyymmdd fields:

ShipDate DMY = DATEVALUE(mid(VW_PowerBI_Analysis[ShipDate],5,2)&"/"&right(VW_PowerBI_Analysis[ShipDate],2)&"/"&left(VW_PowerBI_Analysis[ShipDate],4))

I have also tried with a field created in the Query Editor and set as Data Data type.

I'm expecting a sum of Netsales from Jan 1 2017 up to the last date of 2017 avalaible in my dataset but I'm getting Blank, Nada, Nothing!

I'm sure I'm missing something obvious but everything I'm reading says this should work but it's not!

Any suggestions?

Dan

[v-yuezhe-msft · Accepted solution]

Hi @robofski，

What type of visual do you create? You would need to add filter for the measure to return values. For example, if you create a table visual, add year column in the table visual. If you create a card visual, it will return correct value when you add a slicer to filter the Card, there is a example for your reference.

Alternatively, create a calculated column using the following formula, the column should return values after you add it to your card visual even if you don't user slicer.

Column = TOTALYTD(sum(VW_PowerBI_Analysis[NetSales]),DimCalendar[Date])

Thanks,
Lydia Zhang

[robofski]

@v-yuezhe-msftLydia, I think I have it!  I was filtering with a field in my Fact table not in the calendar table, your post made me think harder so it was the solution!  Thank you.

[robofski]

@v-yuezhe-msftI wish I could say it works but it doesn't!  See screen capture below showing the DAX formulas too.

As I've selected 2017 in the filter I would expect Total Invoiced sales and YTD to be the same but Prior YTD should be different!