Forum Discussion

eric_005's avatar
eric_005
Frequent Visitor
1 year ago
Solved

Calendar table power query does not return correct values using DAX TOTALYTD function

Hi there, I’m quite new to Power BI. My data source is DirectQuery to Analysis Services (AS). I’ve been advised by our BI developer to create my calendar in Power Query instead of using a calculated ...
Need Help
  • eric_005's avatar
    eric_005
    1 year ago

    ToddChitt Sorry Todd, I've been advised by our IT not to proceed on this due to confidentiality. But my issue has been resolved by one of the developer. thanks heaps for your help 🙂

ToddChitt's avatar
ToddChitt
Super User
1 year ago

Can you share the code you used to create the two Calendar tables, along with the measure calculations. Oh, and a screenshot of the model view shoing the relationships in play?

 

Also, please explain why on the first YTD column, the value for December is LESS than all the other months before it. Should the number not be INCREASING as the months go by? Unless the measure is NEGATIVE for only those months.

 

Try this to help us out: Create a table visual and put the Month/Year value in the first column as you have now. Next to that put the base measure you are using. Next to that, put the YTD value. In something like this, the August YTD should equal the July YTD PLUS the August VALUE. Does that make sense?

eric_005's avatar
eric_005
Frequent Visitor
1 year ago

ToddChitt Here they are. 

 

Correct version using Dax calendar:  

YTD_Actual = TOTALYTD(SUM('Finance Aggregate'[Actual_Amount]), '📅CalendarTable'[Date])
 

 

 
Using Power Query Calendar:
 
_YTD_Actual = TOTALYTD(SUM('Finance Aggregate'[Actual_Amount]), _CalendarTable[Date])