12 month rolling chart with Month Slicer

[Anonymous]

I have a model as shown below

I created a visual below. I added filter Reporting Period for 12 months on the filter panel.

I want to create a 12 month rolling chart with a slicer month. I want the slicer MonthYear to filter the chart based on the monthyear selected back to 12 month rolling. For example, when I select Dec-22 on the slicer, then I want the visual to filter from Dec-22 to Nov-21 (12 months).

How can I achieve this? 

[sturlaws]

Hi, @Anonymous,

try this:
https://drive.google.com/file/d/1Bpdg0PASnRxipLCItXPCMR7saq1Op0dG/view?usp=share_link

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[Anonymous]

Hi @Ashish_Mathur - here is the link to the pbix https://drive.google.com/file/d/1RoH7k5QsYCEsb1Qpy2IoBtj1BqEHECLI/view?usp=sharing

[Ashish_Mathur]

Access Denied message.

[Anonymous]

https://drive.google.com/file/d/1RoH7k5QsYCEsb1Qpy2IoBtj1BqEHECLI/view?usp=sharing

please can you check now

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[sturlaws]

Hi, @Anonymous,

You need a separate, unconnected date table. Remove the relative filtering on your measure.

Write a measure like this:

Measure1 =
VAR _date_unconnected =
    CALCULATE ( SELECTEDVALUE ( 'Dates unnconnected'[month] ) )
VAR _date =
    CALCULATE ( SELECTEDVALUE ( Dates[Month] ) )
RETURN
    IF (
        ISBLANK ( _date_unconnected ),
        SUM ( sales[sales] ),
        CALCULATE (
            SUM ( sales[sales] ),
            FILTER (
                ALL ( Dates ),
                _date >= ( _date_unconnected - 365 )
                    && _date <= _date_unconnected
                    && Dates[Month] = _date
            )
        )
    )

where month is the first day of every month.

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.