cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Halleri Frequent Visitor
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

Accepted Solutions
Highlighted

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

 

Alberto

Alberto Ferrari - SQLBI

View solution in original post

9 REPLIES 9
Super User
Super User

Re: Rolling 12m for each date

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


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Halleri Frequent Visitor
Frequent Visitor

Re: Rolling 12m for each date

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

Re: Rolling 12m for each date

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
Halleri Frequent Visitor
Frequent Visitor

Re: Rolling 12m for each date

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?

Highlighted

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

 

Alberto

Alberto Ferrari - SQLBI

View solution in original post

Halleri Frequent Visitor
Frequent Visitor

Re: Rolling 12m for each date

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?

Re: Rolling 12m for each date

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
Halleri Frequent Visitor
Frequent Visitor

Re: Rolling 12m for each date

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?

Re: Rolling 12m for each date

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

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 6 members 1,908 guests
Please welcome our newest community members: