Previous 12 months totals - DAX

DAX DAX Calculations
1

Before I log my problem I’d just like to thank Sam for an awesome service. Registered and paid a short while ago and the whole site is so inspiring and helpful. Anyhow back the confusion of a newbie.

I am scoping out a financial report using David Churchwards Cascading subtotal pattern.
That is all working fine. I have date filter linked to the calendar table and getting MTD YTD etc all working brilliantly from a single month year selection.
Two questions -

  1. How can we get a 12m actual history graph, - previous 12 m from selected single month date. So if Nov 2018 selected - x axis will be Dec 17 to Nov 17 with each month
  2. Having achieved that how do we put another line on it with the previous 12 months rolling total.
    So Nov will have Dec to Nov actual; Oct will have Nov - Oct actual etc.

I have googled this one till I’m blue in the face and I understand David Churchwards idea of using a detached date table. But then other time intelligence seem to fail and there has to a way to do this with the proper calendar table. I’ve tried various combinations of ALL but I just can’t see it!!!

Help
Pete
PS still awesome site

2

Pete,

After looking up David Churchward’s method, I think what you are looking for was shown by Sam in this video

Take a look and see if it helps.

Guy

Enterprise%20DNA%20Expert%20-%20Small

3

Hi Guy
I had already downloaded Sam’s video but it unfortunately it doesn’t address the issue.
Sam’s works because he’s looking at full year results. My issue is a monthly one.

The problem is to get a chart for the last 12 months from a single month selection in a slicer.
I understand the problem is to do with context but I’m not sure how to widen the context for the measure that will go in the chart.
Two measures required – 1. month profit and 2. previous 12 months profit to the month in question.

I have looked at Sam’s stuff on cumulative but can’t find an example with date slicer.

My business is a group of 9 motor dealerships which suffer from huge volatility and seasonality, hence the need for rolling 12 months.

Regards
Pete

4

Pete,

If I’m understanding you this may help to get you started

Cumulative Total = CALCULATE (
    SUM ( 'Table'[Profit] ),
    FILTER (
        ALLSELECTED(Dates),
        'Dates'[Date] <= MAX ( 'Dates'[Date] )
    )
)

The ALLSELECTED restricts the table returned to only the time range selected. This time range should be able to be modified for what you are after.

Guy

Enterprise%20DNA%20Expert%20-%20Small

5

Pete,

Another option is to drop the Date into a page filter; then select relative filtering under date and select what you want to see relative to the slicer.

Guy

Enterprise%20DNA%20Expert%20-%20Small

6

Thanks Guy
I tried the page filter earlier and whilst helped get rid of some clutter it didn’t solve this one.
I’m not sure your DAX suggestion will work either because my date slicer will on have a single month selected.
I have been giving it some thought and it’s taken me down a slightly different road as a workaround. I will create two pages - one with “traditional” accounting info on it which will be fairly static in nature (MTD, YTD, Act, Bud + variances) with some visualisations on the static piece. Then I’ll have a second page to do the trending, rolling 12 etc where I will be able to widen the date slicer and then everyting should work.
So whilst the technical problem still exists, with some thought on what I’m presenting I should get sorted. I reckon you need a whizz liked Sam to write something seriously clever to do what I’m suggesting. So nice to have but not a show stopper!
Thanks for your help here and promptness in responding. No doubt I’ll be here again soon!!
Pete