Rolling 13 Months with one Date Slicer only

[Anonymous]

Hi,

I have a SSAS tabular model as my datasource and have a standard Kimball Star Schema in the backend.

I'm wanting to show rolling sales in one graph and be able to slice the rest of the dash by the month picked in the slicer.

So, the slicer should filter all widgets on the dash by a month, and should set the rolling 13 months to show the month picked minus 13 months. If no month is picked, all the measures show the current values to date (eg Sales YTD).

Currently, the date slicer on the dashboard is filtering my 13 months for one month only because it is joined to the Sales Fact table and is using that join. I have tried a separate rolling 13 month slicer, which works very well, but this will not be accepted by the business as they don't want to be picking a month from both slicers.

There has to be some sort of table that can be created on the fly in DAX that would make this dynamic, where the date range filters on the Sales Fact rather than by the date relationship.

My current code looks like this  (i've borrowed from other codes in this forum to try and get the result):

Sales Rolling 13 Months :=
VAR Maximum_Date =
    MAX ( 'Date Rolling 13 Months'[Month End Date] )
VAR Minimum_Date =
    DATE ( YEAR ( Maximum_Date ) - 1, MONTH ( Maximum_Date ), 1 )
VAR Selected_Date =
    MAX ( 'Date'[Date Value] )
RETURN
    CALCULATE (
        IF (
            Selected_Date <= Maximum_Date
                && Selected_Date >= Minimum_Date,
            SUM ( SALES[Actual Value] ),
            BLANK ()
        ),
        ALL ( 'Date' )
    )
&& Selected_Date >= Minimum_Date,
sum(SALES[Actual Value]),
BLANK ()
), ALL('Date'))

Love some help with this, thanks  in advance!!!

[v-cherch-msft · Accepted solution]

Hi @Anonymous 

Please check below measure:

Measure = 
CALCULATE (
    SUM ( Data[Sales] ),
    FILTER (
        ALL ( 'Data'[Order] ),
        'Data'[Order]
            >= SELECTEDVALUE ( 'Calendar'[Order_Calendar] ) - 12
            && Data[Order] <= SELECTEDVALUE ( 'Calendar'[Order_Calendar] )
    )
)

Regards,

[Anonymous]

Hi @v-cherch-msft ,

Thank you very much for the reply - I thought I'd never get an answer!

I unfortunately cannot use SELECTEDVALUE as my SSAS tabular does not support that function.

Would you have a substitute for it by any chance?

Thanks again,

Basia

[v-cherch-msft]

Hi @Anonymous 

Try MAX function to replace it.

Regards,

[Anonymous]

Thanks, will do.