Forum Discussion

Microsoft Employee

6 years ago

Solved

Rolling 3 month

I would like to have rolling data for calculation of [measure] for 3 months. please help me to create it. thanks

date format is in YYYYMM format, which is text in data type now.

for exmaple

data rolling data

202001 300 300

202002 100 400

202003 700 1100

202004 200 1000

Super User

Follow the steps below:

Power Query:

Date.EndOfMonth(#date(
    
    Number.FromText
   (Text.Start(Number.ToText([Date]),4)),Number.FromText(Text.End(Number.ToText([Date]),2)),1))

DAX:

Month_End_DAX = EOMONTH(DATE(LEFT('Table'[Date];4);RIGHT('Table'[Date];2);1);0)

Now add the following measure to your model:

Rolling 3 month = CALCULATE(SUM('Table'[Data]);DATESINPERIOD('Table'[Month_End];MAX('Table'[Month_End]);-3;MONTH) ; ALLSELECTED('Table'[Data]))

Should return expected result.