powerbidaxssascube

DAX - YoY Calculation don't work on higher Date level

I'm trying to create a Year on Year and Month on Month Calculation using DAX for my Cube. Here's my formula to get Last Month and Last Year:

Revenue_LM:= CALCULATE(SUM([CreditDebit]), DATEADD('DIM Calendar'[CalendarDate], -1, MONTH)) 
Revenue_LY:= CALCULATE(SUM([CreditDebit]), DATEADD('DIM Calendar'[CalendarDate], -1, YEAR))

It works well, if I break it down using the "Date" column in my calendar Table:

I only have data for start of month because that's when we do the billing.

enter image description here

Problem is when I do it at a year level, the Revenue_LY formula no longer work:

enter image description here

Same goes with Revenue_LM formula when I do it at a month level. For LY, I've also tried using SAMEPERIODLASTYEAR but I had the same issue.

Is there anything I'm missing?

Thanks, Kim

Solution

  • Ensure your Dim Calendar is marked as a Date table in your SSAS cube.