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
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?
Most Valuable ProfessionalWhy 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.com
Alberto
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?- AlbertoFerrari
Just author your measures adding some extra logic to blank out the future.
Something like this:
IF ( MAX ( Date[Date] ) <= TODAY (), [YourMeasure], BLANK () ) Or, if you don't want to rely on TODAY: IF ( MAX ( Date[Date] ) <= CALCULATE ( MAX ( Fact[Date] ), ALL ( Date ) ), [YourMeasure], BLANK () )The upper limit always require some attention, as you need to understand what you want to use as the upper limit, but this guarantees you that points in the future will not be plotted.
It is a very common pattern, for any measure that shows in the future (for example, YTD, MTD and similar calculations) and you do not want to plot thos values anyway.
Have fun with DAX!
Alberto Ferrari
http://www.sqlbi.comThis was very helpful and applicable on a lot of things I'm currently working on. But as of what I understand, it's not applicable to limit the date slicer?
Hi!
An old thread but has something changed or would you be able to explain the below.
This gives me 2019-11-19:Last Date in the Period for DatesInPeriod =LASTDATE(DATESINPERIOD(Dim_Date[Date];LASTDATE(Dim_Date[Date]);-1;YEAR))but why I get 2018-12-01 from the below and not 2018-11-20:
First Date in the Period for DatesInPeriod =
FIRSTDATE(DATESINPERIOD(Dim_Date[Date];LASTDATE(Dim_Date[Date]);-1;YEAR))The below gives 2018-11-20 as expected.First Date in the Period for DatesInPeriod =
FIRSTDATE(DATESINPERIOD(Dim_Date[Date];LASTDATE(Dim_Date[Date]);-365;DAY))I'm confused and because of this the previously made Rolling 12m calculation doesn't seem to work and I have used another way to calculate it which works:Sales 12m= CALCULATE (Sum(Sales);DATESBETWEEN (Dim_Date[Date];NEXTDAY ( DATEADD( LASTDATE(Dim_Date[Date]);-1;YEAR));LASTDATE ( Dim_Date[Date] )))I have marked the date table as calendar table in the model and I'm using Version: 2.75.5649.582 64-bit. I'm afraid I will need to make quite a many changes if I will need to make the same changes to all the rolling 12 months calculations in different models.
