Forum Discussion

KatkaS's avatar
KatkaS
Icon for Post Patron rankPost Patron
3 years ago

12M rolling with filter

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).

 

AREAValuePeriod
Finance100001/01/2023
IT2000

01/01/2023

Sales300001/01/2023
Finance1200

01/02/2023

IT250001/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!
Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    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

  • KatkaS's avatar
    KatkaS
    Icon for Post Patron rankPost Patron
    3 years ago

    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!
  • KatkaS's avatar
    KatkaS
    Icon for Post Patron rankPost Patron
    3 years ago

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