Forum Discussion
Rolling 12m for each date
Why do you have that additional reference to the Date extension? The formula should look like this:
test 12m = CALCULATE ( SUM ( Sales[SalesAmount] ); DATESINPERIOD ( DateTable[Date]; CALCULATE ( MAX ( DateTable[Date] ) ); -1; YEAR ) )Notice the missing .[Date] in the first parameter of DATESINPERIOD (DateTable[Date]).
The extension created by Power BI contains the full year. My advice is always the same with auto date/time: disable it, learn time intelligence and forget about its existence :)
Anyway, removing that reference should fix the problem although a date table ending in August 26 is not a best practice, the best would be to protect your code using an IF statement that blanks the measure. With that said, in your special case, you can live with an incomplete date table.
Have fun with DAX!
Alberto Ferrari
http://www.sqlbi.comAlberto
Most Valuable ProfessionalYep, a MAX would solve the issue. Moreover, you can make it much easier relying on time intelligence functions, like this:
CALCULATE (
SUM ( Sales[SalesAmount] ),
DATESINPERIOD ( 'Date'[Date], LASTDATE ( 'Date'[Date] ), -1, YEAR )
)Your problem is the relationship, ALL makes DAX ignore it, DATESINPERIOD does it automatically.
Have fun with DAX!
Alberto Ferrari
http://www.sqlbi.com
Hi Alberto,
Thank you very much for your answer!
I used your formula with a little modification.test 12m = CALCULATE ( SUM ( Sales[SalesAmount] ); DATESINPERIOD ( DateTable[Date].[Date]; CALCULATE ( MAX ( DateTable[Date] ) ); -1; YEAR ) )And I'm very close to the desired result:
My [DateTable] looks like this:DateTable = ADDCOLUMNS ( CALENDAR ( MIN ( 'Sales'[Calendar day] ); MAX ( 'Sales'[Calendar day] ) ); "DateId"; FORMAT ( [Date]; "YYYYMMDD" ); "Year #"; YEAR ( [Date] ); "Year/Month"; FORMAT ( [Date]; "YYYY/MM" ); "Month #"; FORMAT ( [Date]; "MM" ); "Month name short"; FORMAT ( [Date]; "mmm" ); "Month name long"; FORMAT ( [Date]; "mmmm" ); "Week #"; WEEKNUM ( [Date] ); "DayOfWeek #"; WEEKDAY ( [Date] ); "Day #"; FORMAT ( [Date]; "dd" ); "Day name"; FORMAT ( [Date]; "dddd" ); "Quarter"; FORMAT ( [Date]; "Q" ) )
The remaining problem is that the chart plots out dates beyond Aug 26, 2017. The DateTable only contains dates up to that date, and so does [Sales].
What is the cause of this? And how can I fix it?- AlbertoFerrari
Why do you have that additional reference to the Date extension? The formula should look like this:
test 12m = CALCULATE ( SUM ( Sales[SalesAmount] ); DATESINPERIOD ( DateTable[Date]; CALCULATE ( MAX ( DateTable[Date] ) ); -1; YEAR ) )Notice the missing .[Date] in the first parameter of DATESINPERIOD (DateTable[Date]).
The extension created by Power BI contains the full year. My advice is always the same with auto date/time: disable it, learn time intelligence and forget about its existence :)
Anyway, removing that reference should fix the problem although a date table ending in August 26 is not a best practice, the best would be to protect your code using an IF statement that blanks the measure. With that said, in your special case, you can live with an incomplete date table.
Have fun with DAX!
Alberto Ferrari
http://www.sqlbi.comAlberto
You're completely right, the extra ".[Date]" should be left out. I marked the date measure as "Date" rather than "Date Hierarchy", and it worked, but the extra ".[Date]" was needed to get somewhat the desired result (as in screen shot) with "Date Hierarchy".
I obviously need to take you advice and go back and study time intelligence a bit more since I'm currently using the "auto time intelligence".
However, if I have a Date Table with dates beyond what I have sales for: How do I get the date slicer to only show dates for which I have sales data? Applying a page level filter with a static date would require the report creator to update the value at each update, which doesn't seem like a very good solution...
Could you point me to some material for how I handle time when "auto time intelligence" is deselected? Do I build my own date hierarchy?
