Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi guys,
for some reason the last summarize is not showing up!
Any ideas?
thank you
John
Solved! Go to Solution.
The 3rd SUMMARIZE appears to be returning a blank row, suggesting that 30 Jan 2017 is not in the DimDates table.
Does your date table contain a contiguous series of dates?
I tested with a dummy date table ending with 30 Apr 2017 (created with CALENDAR function) and the DAX query returned three rows.
The 3rd SUMMARIZE appears to be returning a blank row, suggesting that 30 Jan 2017 is not in the DimDates table.
Does your date table contain a contiguous series of dates?
I tested with a dummy date table ending with 30 Apr 2017 (created with CALENDAR function) and the DAX query returned three rows.
It does have contiguous series
I ended up using the following dax instead
EVALUATE Union ( SUMMARIZE ( DimDates ,"Date Reference","MTD" ,"Date",LASTDATE(DimDates[Dates]) ) , SUMMARIZE ( DimDates ,"Date Reference","Last Three Months" ,"Date", EOMONTH(Date(Year(LASTDATE(DimDates[Dates])),Month(LASTDATE(DimDates[Dates]))-3,Day(LASTDATE(DimDates[Dates]))),0) ) )
is this the best practice?
Am I supposed to have my dimdates by day?
My Dates are being generated by sql server the source of the dates is the FactTable that has only the end of the month dates.
Do you think if i switched the dates to the begining of the month the dateadd function will work?
Thanks
Yes best practice for a date dimension table is to have a row per day for a contiguous block of days.
Your original DAX query would have worked if you had used month-start dates, due to the way DATEADD works.
Your new method works because EOMONTH doesn't require the resultant date to be present in the Date column.
Regards,
Owen 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |