How to create a matrix with12 months of rolling/trailing data in Power BI

[Anonymous]

This is my input file, having more than 24 months and more than a dozen countries of data. 

Now I want to create a matrix in the PBIX, with 12 months of trailing data from a date slicer.

That is, I will have a date slicer and I would like to see the previous 12 months of data from the selected month.

For example, if I select Jan'21 in my slicer I want the matrix to have data from Jan'21 to Feb'20(12 months) and if I select Nov'20 I want the matrix to show data from Nov'20 to Oct'19(12 months). 

I have tried implementing this in Table visual and it works fine in table visual. It even works in the matrix till I don't have rows, as soon as I drag the Class column in the Rows section it does not work. 

Thank you. Any help would be appreciated.

[Anonymous · Accepted solution]

This video  helped me.

Thanks.

[v-kkf-msft]

Hi @Anonymous ,

Try the following formula:

Measure = 
var SelectDate = MAX('Calendar'[Date])
var RollingMonths = 
    CALCULATETABLE(
        DISTINCT('Calendar'[Month-Year]),
        FILTER(
            ALL('Calendar'),
            'Calendar'[Date] <= SelectDate
            && 'Calendar'[Date] > EOMONTH(SelectDate,-12)
        )
    )
return 
    COUNTROWS(
        INTERSECT(
            RollingMonths,
            DISTINCT('Table'[Date])
        )
    )

Then filter Date column in matrix by Measure.

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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

[Fowmy]

@Anonymous 

Refer to the video from sqlbi.com:
https://www.youtube.com/watch?v=d8Rm7dwM6gc&t=1s