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

[eric_005]

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 🙂

[ToddChitt]

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?

[eric_005]

@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"

[ToddChitt]

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.

[eric_005]

@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:

[ToddChitt]

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?

[eric_005 · Accepted solution]

@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]

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]

@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])