Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Halleri
Frequent Visitor

Rolling 12m for each date

Hi, I got a date table [DateTable] and a table of sales [Sales]. I've successfullt created a rolling 12m for sales:

RoSales 12m =
VAR minDate =
    DATE ( YEAR ( MAX ( DateTable[Date] ) ); MONTH ( MAX ( DateTable[Date] ) ) - 12; DAY ( MAX ( DateTable[Date] ) ) )
RETURN
    CALCULATE (
        SUM ( Sales[Sales] );
        FILTER (
            DateTable;
            DateTable[Date] > minDate
                && DateTable[Date] <= MAX ( DateTable[Date] )
        )
    )

What I want to do, and failed repeatedly to, is to get a chart with dates on the X-axis and the Sales for the last 12 months at each date on the Y-axis.
Example if in table: 

 

 

|____Date_______|_Rolling 12__|
| 10 Aug 2017    | 100K           |
| 11 Aug 2017    | 101K           |
| 12 Aug 2017    | 108K           |
| 13 Aug 2017    | 107K           |

 

All my attempts results in the measure taking in the context of the date and plots out the sum for that date, when I really want the sum of the whole last year with the date as end point. Is this possible?

Cheers!

 

1 ACCEPTED SOLUTION

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.com

 

Alberto

Alberto Ferrari - SQLBI

View solution in original post

10 REPLIES 10

Yep, 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

Alberto Ferrari - SQLBI

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:
chart_12m_sales_days.png
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?

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.com

 

Alberto

Alberto Ferrari - SQLBI

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.  

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?

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.com

Alberto Ferrari - SQLBI

This 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?

Nope. Nevertheless, you have some other nice options.

 

For example, you can create a calculated column in your date table with code like this:

 

IsPast = Date[Date] <= MAX ( Fact[Date] )

or

IsPast = Date[Date] <= TODAY ()

 

Then, in the report you can apply a single filter for IsPast=TRUE, so that any date in the future will not show up. Being a calcuated column, it is recomputed at every data refresh, so you do not need to modify the report filter to show new dates when facts appear for that date.

 


Have fun with DAX!

Alberto Ferrari
http://www.sqlbi.com

Alberto Ferrari - SQLBI
Greg_Deckler
Super User
Super User

Perhaps try an ALL(DateTable) or something like that to specify the context within the measure.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

I've tried several solutions where I add ALL(DateTable)/ALL(DateTable[Date])/ALL(DateTable[Date].[Date] and so on, without any success.
In my measure above I specify the date range which references the slicer. The date in the formula need to reference the date point in the chart rather than the measure. So in some way ignore the context of sales for the date in the chart, and instead add context between the formulas date range and the date in the chart.

Does my logic compute? Smiley Tongue

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.