Need help to show Rolling 12 month for last 3 Years or 36 months

[Anonymous]

Hi Experts,

Need help to show Rolling 12 month for last 3 Years (36 Months).

Here rolling 12 months means on the x-axis each month will have SUM of the previous 12 months starting from respective months on the x-axis.

For example
If I select Last 1 Year from the relative slicer. (Today is 27 Jan 2021)
then trend lines should show 36 months on the x-axis from Jan 2018 to Dec 2020 (Last 36 months).

The tricky part is rolling sum for the last 12 months on each month like

• On Jan2018 data should be SUM from feb2017 to jan2018,
• On Feb2018 data should be SUM from Mar2017 to Feb2018,
• On Mar2018 data should be SUM from Apr2017 to Mar2018, so on
•  
•  
• On Dec2020 data should be SUM from Jan2020 to Dec2020

Here is Dax to show period ranges calculated column :

Month Period Test = 
VAR __year = Year(Datum[Datum])
VAR __month = MONTH(Datum[Datum]) + 1
VAR _year2 = IF(__month = 1, __year, __year + 1)
VAR _month2 = IF(__month = 1, 12, __month - 1)
VAR _date1 = DATE(__year, __month, 1)
VAR _date2 = DATE(_year2, _month2, 1)

RETURN
FORMAT(_date1, "MMM YYYY") & " - " & FORMAT(_date2, "MMM YYYY")

Here is a measure to calculate the rolling 12-month sum:

Rolling 12 Month  =

VAR _date = MIN ( DimDate[Date] )
VAR _year = YEAR ( _date )
VAR _month = MONTH ( _date ) + 1
VAR _year2 = IF ( _month = 1, _year, _year + 1 )
VAR _month2 = IF ( _month = 1, 12, _month - 1 )
VAR _startdate = DATE ( _year, _month, 1 )
VAR _enddate = EOMONTH ( DATE ( _year2, _month2, 1 ), 0 )
VAR _currentyear = YEAR ( TODAY () )
VAR _currentmonth = MONTH ( TODAY () )

RETURN
IF (
AND ( _year2 >= _currentyear, _month2 >= _currentmonth ),
BLANK (),
CALCULATE (
[Sales],
ALL ( 'DimDate' ),
'Datum'[Datum] >= _startdate,
'Datum'[Datum] <= _enddate
)
)

Here is the rolling 12-month logic I've applied.

Two trends are shown for better understanding.

Here is the PBIX file

Thanks

[Anonymous]

Hi @Anonymous ,

1. "If I select Last 1 Year from the relative slicer. (Today is 27 Jan 2021) ,then trend lines should show 36 months on the x-axis from Jan 2018 to Dec 2020 (Last 36 months)"

To do this , please delete the relationship(or make it inactive) between these two tables

Then you could use the following formula to create a measure :

Filter Measure=
VAR _minDate =
    EDATE ( MAX ( 'Datum'[Datum] ), -36 )
VAR _maxDate =
    EDATE ( MAX ( 'Datum'[Datum] ), -1 )
RETURN
    IF (
        MAX ( 'Statistiek'[Factuurdatum] ) >= _minDate
            && MAX ( 'Statistiek'[Factuurdatum] ) <= _maxDate,
        1,
        0
    )

2.calculate rolling sum for the last 12 months on each month:

Sum Measure =
CALCULATE (
    SUM ( 'Statistiek'[Bedragtransport] ),
    DATESINPERIOD (
        'Statistiek'[Factuurdatum],
        MAX ( 'Statistiek'[Factuurdatum] ),
        -1,
        YEAR
    )
)

After applying Filter Measure to visual's filter pane (set as "=1") ,the output is shown below:

Here is the pbix file.

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Good work BTW @Anonymous

Just update your Dax filter measure. We need to use min date not max in order to reflect the last 2 years

Filter Measure = 
VAR _minDate =
    EDATE ( MIN ( 'Datum'[Datum] ), -24 )
VAR _maxDate =
    EDATE ( MAX ( 'Datum'[Datum] ), -1 )
RETURN
    IF (
        MAX ( 'Statistiek'[Factuurdatum] ) >= _minDate
            && MAX ( 'Statistiek'[Factuurdatum] ) <= _maxDate,
        1,
        0
    )

is it possible to do so with an active relationship?

because the same trend I need to visualize with other visuals and the same date slicer will use for other cards. That would not work I think so. Can you help me more in this regard, please?

[Anonymous]

Hi @Anonymous,
Thanks for the above solution.
But In the trend line x-axis should be like the below image.

And one more thing when 
If I select Last 1 Year from the relative slicer. (Today is 27 Jan 2021)
then trend lines should show 36 months on the x-axis from Jan 2018 to Dec 2020 (Last 36 months).
And If I select the Last 2 Years from the relative slicer. (Today is 27 Jan 2021)
then trend lines should show 36 months on the x-axis from Jan 2017 to Dec 2019 (Last 36 months) and So On.

[Anonymous]

Help, please.

@amitchandak @Anonymous @parry2k @Anonymous @MFelix