Rolling average of a measure

[e2liu77]

Hi

I need to calculate a rolling 3 months average of a measure.

In my table, I have a Date column, but the monthly measurement period is not from 01-30/31 (like Jan is from 01.Jan-31.Jan), but from 15 previous month -14 current month (like Jan is 15.Dec - 14.Jan) .

I create a new column  to identify the measurement period.

ReportMonth =

if(DataSource[Day]>= 1 && DataSource[Day] <= 14, DataSource[Month], if(DataSource[Month] =12, 1, DataSource[Month]+1))

Reporting Months =

if(DataSource[Month] =12 && DataSource[ReportMonth] = 1, FORMAT(DataSource[Year]+1,"####") & "-" &if(DataSource[ReportMonth]<10,"0"&FORMAT(DataSource[ReportMonth], "##"), FORMAT(DataSource[ReportMonth], "##")), FORMAT(DataSource[Year],"####") & "-" & if(DataSource[ReportMonth]<10, "0"& FORMAT(DataSource[ReportMonth], "##"), FORMAT(DataSource[ReportMonth], "##")))

I have a measure called "Testing Scorecard", it is a calculated percentage.

Now I need to calculate the average of 3 months measure

2018-03 should be 24.75%

2018-04 should be avg(24.75%, 21.95%)

2018-05 should be avg(24.75%, 21.95%, 26,33%)

2018-06 should be avg(21.95%, 26,33%, 36.76%)

Any idea how to achieve this?

Thanks!

[v-frfei-msft]

Hi @e2liu77 ,

We can create a measure as below.

Measure = 
SUMX (
    FILTER ( ALL ( Table1 ), Table1[Column] <= MAX ( Table1[Column] ) ),
    [Testing Scorecard1]
)
    / CALCULATE (
        COUNTROWS ( Table1 ),
        FILTER ( ALL ( Table1 ), Table1[Column] <= MAX ( Table1[Column] ) )
    )

Pbix as attached.

Regards,

Frank

[e2liu77]

Hi Frank,

Thanks for helping me.

I checked your PBIX file and found that your [Test scorecard] is a column, but mine is a measure. With your measure DAX formula it will not work.

I attached my PBIX for your reference. Any idea how to change my formula?

[v-frfei-msft · Accepted solution]

Hi @e2liu77 ,

Please check the following steps as below.

1. Create a date table and create relationship between the fact table and it.

Table = CALENDAR(DATE(2017,01,01),DATE(2019,12,31))

2. Create two calculated column in your fact table as below.

YearMonth = var _date = DATEADD('Table'[Date],1,MONTH)
return
YEAR(_date)*100+MONTH(_date)

Year Month 2 = var _date= DATEADD('Table'[Date],-1,MONTH)
return
YEAR(_date)*100+MONTH(_date)

3. Then we can create a measure to achieve your goal.

average 3 = 
SUMX (
    FILTER (
        ALL ( DataSource[YearMonth] ),
        DataSource[YearMonth] <= MAX ( DataSource[YearMonth] )
            && 'DataSource'[YearMonth] >= MAX ( DataSource[Year Month 2] )
    ),
    [Test Scorecard]
)
    / CALCULATE (
        DISTINCTCOUNT ( DataSource[YearMonth] ),
        FILTER (
            ALL ( DataSource[YearMonth] ),
            DataSource[YearMonth] <= MAX ( DataSource[YearMonth] )
                && 'DataSource'[YearMonth] >= MAX ( DataSource[Year Month 2] )
        )
    )

Pbix as attached.

Regards,

Frank