12M rolling with filter

[KatkaS]

Hi,

could somebody please help me how to create 12M rolling measure with FILTER?

I'm trying to create 12M rolling measures, I used two with the same results, they seem to be ok (1st mesure and 2nd measure)

Problem starts, when I try to add FILTER on a specific area that I have in the data (2nd measure with problem).

AREA	Value	Period
Finance	1000	01/01/2023
IT	2000	01/01/2023
Sales	3000	01/01/2023
Finance	1200	01/02/2023
IT	2500	01/02/2023

1st measure:

# 12M ROLLING = var __lastVisibleDate = LASTDATE( 'PERIOD'[Report Date] )

var __canMove12MBack =

    NOT ISBLANK( dateadd( __lastVisibleDate, -1, Year) )

var __result =

    CALCULATE(

        SUM( '05_FIN-050 and FIN-902 appended'[Value]),

        DATESINPERIOD(

            'PERIOD'[Report Date],

            __lastVisibleDate,

            -1,

            Year

        )

    )  

return

if( __canMove12MBack, __result )

2nd measure:

# 12M = CALCULATE(sum('05_FIN-050 and FIN-902 appended'[Value]),DATESINPERIOD('PERIOD'[Report Date],MAX('PERIOD'[Report Date]),-12,MONTH))

2nd measure with filter - WRONG RESULT:

# 12M = CALCULATE(sum('05_FIN-050 and FIN-902 appended'[Value]), 

FILTER ('05_FIN-050 and FIN-902 appended','05_FIN-050 and FIN-902 appended'[AREA] = "Finance")

DATESINPERIOD('PERIOD'[Report Date],MAX('PERIOD'[Report Date]),-12,MONTH))

Thank you very much!

[KatkaS]

Hello, could anyone check the filter question in my reply on 08-09 please? Thank you!!

[KatkaS]

@amitchandak Thank you very much, Amit, for your prompt reply!

I tried your measure (with relationship between period table and period of the data) and and it worked well, but when I added there FILTER on area, it showed only the current period..

Measure with filter: 

# 12M rolling finance = CALCULATE(sum('05_FIN-050 and FIN-902 appended'[Value]),FILTER('05_FIN-050 and FIN-902 appended','05_FIN-050 and FIN-902 appended'[AREA] = "Finance"), DATESINPERIOD('PERIOD'[Report Date],MAX('PERIOD'[Report Date]),-12,MONTH))

Could you check what could be wrong? Thank you!

[amitchandak]

@KatkaS , You should use a date table joined with date of your table in such cases

example
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-12,MONTH))

or

Rolling 12 = CALCULATE([Net], WINDOW(-11,REL, 0, REL, ADDCOLUMNS(ALLSELECTED('Date'[Month Year],'Date'[Month Year Sort] ),ORDERBY([Month Year Sort],asc)))

Rolling Months Formula: https://youtu.be/GS5O4G81fww

Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc
https://medium.com/@amitchandak/power-bi-window-function-3d98a5b0e07f