Help with Moving Average of average measure

[Anonymous]

Hi,

 i need help with moving average of measure. I am attaching excel file with data and output and also pbix file which have all the data imported

Excel File 

pbix File 

Thanks in Advance.

[Nathaniel_C]

Hi @Anonymous ,

Looking at your pbix, which measure are you working on?

Nathaniel

[Anonymous]

Thanks for your quick response. I was trying different things so please ignor my measures just bill to book ratio measure is working for simple ratio but moving average of bill to book for 4 weeks and 13 week is not working as per the output sheet in excel.

[MFelix · Accepted solution]

Hi  @Anonymous ,

You need to make the following two measures below:

4week avg = 
VAR temp_table =
    SUMMARIZE (
        FILTER (
            ALL ( 'fiscal week' );
            'fiscal week'[Date].[Date] <= MAX ( 'fiscal week'[Date] )
                && 'fiscal week'[Date].[Date]
                    >= MAX ( 'fiscal week'[Date] ) - 28
        );
        'fiscal week'[Fiscal Week];
        "@bookRation"; [Book to Bill Ratio]
    )
RETURN
   IF(COUNTX(temp_table;[@bookRation]) < 4; BLANK();  AVERAGEX ( temp_table; [@bookRation] ))

13week avg = 
VAR temp_table =
    SUMMARIZE (
        FILTER (
            ALL ( 'fiscal week' );
            'fiscal week'[Date].[Date] <= MAX ( 'fiscal week'[Date] )
                && 'fiscal week'[Date].[Date]
                    >= MAX ( 'fiscal week'[Date] ) - 91
        );
        'fiscal week'[Fiscal Week];
        "@bookRation"; [Book to Bill Ratio]
    )
RETURN
   IF(COUNTX(temp_table;[@bookRation]) < 13; BLANK();  AVERAGEX ( temp_table; [@bookRation] ))

Check PBIX file attach.

Regards,

MFelix

[Anonymous]

@MFelix Thank you so much sir.