Forum Discussion
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 table. However, the TOTALYTD function with the new calendar is not returning the correct values in my tables. Has anyone encountered the same issue before? I’ve tried exploring all possible options, but I can’t seem to find a solution. I’ve ensured that the same relationships are in place and marked the table as a date table, but the issue persists. Here is a screenshot of both results. Sorry, I can't share my report as it is confidential. Thanks in advance 🙂
8 Replies
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?
ToddChitt sorry I missed the calendar code.
Correct Dax = then added additional columns
📅CalendarTable = CALENDARAUTO()Power Query Calendar :let
// Define the start and end date for the calendar
StartDate = #date(2017, 1, 1), // Change to your desired start date
EndDate = #date(2034, 12, 31), // Change to your desired end date
// Generate a list of dates from the start to the end date
DateList = List.Dates(StartDate, Duration.Days(EndDate - StartDate) + 1, #duration(1, 0, 0, 0)),
// Convert the list of dates to a table with a single Date column
DateTable = Table.FromList(DateList, Splitter.SplitByNothing(), {"Date"}),
#"Changed Type" = Table.TransformColumnTypes(DateTable,{{"Date", type datetime}})
in
#"Changed Type"Does it not strike you as odd that the July 2024 YTD number of $2,194,946,610.31 MINUS the August (incorrect YTD number) $383,419,666.63 exactly equals the August 24 YTD number of $1,811,526,943.68, and so on down the line?
Still curious about the columns that participate in the relationships. If it is the Month and Year, say, "202407" for July, there are MANY of those in the Date tables, so how can you have a Many to ONE relationship?!?
I need to see sample data from all the tables.
ToddChitt Yup I've noticed that as well. My purpose of the YTD as shown on the correct DAX calendar is to show the YTD actual amount at that time. I'm not really sure what's going on with Power Query Calendar. Basically, that code will show only first column (Date) and then I do DAX measures for the rest to populate the other columns. I use YearMonthNumber as my unique ID to create a relationship because the main table (Finance Aggregate) has that format too and not the actual date. I did the same process ont he correct DAX calendar.
Only many to one works because of this error:
What is the granularity of the Calendar table? Is it Year and Month, or is it by day? Can you show some sample data?
And what is the date granularity of the fact table? Also by Year and Month?
Can you show us some sample Date data from both tables?
