Forum Discussion
Calendar table power query does not return correct values using DAX TOTALYTD function
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?