Forum Discussion
Calendar table power query does not return correct values using DAX TOTALYTD function
ToddChitt sorry I missed the calendar code.
Correct Dax = then added additional columns
// 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:
So the column [YearMonthNumber] is in the format of YYYYMMDD. Though you have not shown it, can I assume that values in the column [dim_financial_month_key] are also in that same format?
How many rows (roughly) do you have for any one given month in table Financial Aggregate? Can you show a redacted screen shot of that data? Please include in the screenshot data for both the [dim_financial_month_key] and [Actual Amount] columns.
Can you confirm, by looking at the Model view, that the two Calendar Date tables are on the ONE side of their respective Many-to-One relationships? (The ONE side has a 1 right next to the table where the relationship line comes in, the MANY side has a * next to the table).
And finally, please set the Filter direction to Single. When you do that, the arrow on the relationship line should point to the Financial Aggregate (Many side) table.
Can you confirm that there are NO OTHER filters in play on the visual you showed in the original post?
