Forum Discussion

significant's avatar
significant
Frequent Visitor
8 years ago

Calculate weekly moving average for blank values and beginning of year

I need to create a rolling avarage with a window of 5 weeks.   The following measure: CALCULATE(if(countrows(values(logData[dateStampWeek])) = 1, CALCULATE( AVERAGEX(VALUES(logData[dateStampWe...
Need Help
v-lili6-msft's avatar
v-lili6-msft
Icon for Community Support rankCommunity Support
8 years ago

hi, significant

You may try to use this measure:

Measure 2 = var numberweek = CALCULATE (
    IF (
        COUNTROWS ( VALUES ( logData[dateStampWeek] ) ) = 1,
        CALCULATE (
            COUNTROWS(  VALUES ( logData[dateStampWeek] )),
            logData[dateStampWeek] <= VALUES ( logData[dateStampWeek] )
                && logData[dateStampWeek]
                    > VALUES ( logData[dateStampWeek] ) - 5
        ),
        BLANK ()
    )) return
CALCULATE (
    IF (
        COUNTROWS ( VALUES ( logData[dateStampWeek] ) ) = 1,
        CALCULATE (
            SUMX(  VALUES ( logData[dateStampWeek] ), CALCULATE(SUM(logData[sumHoursDayPart])) ),
            logData[dateStampWeek] <= VALUES ( logData[dateStampWeek] )
                && logData[dateStampWeek]
                    > VALUES ( logData[dateStampWeek] ) - 5
        )/numberweek,
        BLANK ()
    ))

Result:

and weeknumbers is you bound the calculation to a year which of the selected user input

 

 

Best Regards,

Lin