Forum Discussion

significant's avatar
significant
Frequent Visitor
7 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[dateStampWeek]), logData[sumHoursDayPart])
		 ,logData[dateStampWeek] <= VALUES(logData[dateStampWeek]) &&  logData[dateStampWeek] > VALUES(logData[dateStampWeek])-5
		)
, blank()), DATESBETWEEN('Calendar'[Date],STARTOFYEAR('Calendar'[Date]),ENDOFMONTH('Calendar'[Date])))

works partly and I get stuck with two issues, namely:

 1 for the first 4 weeks the numbers of last year are not taking into account

 2 for blank values the avarage is not calculated

The image shows the weeknumbers, values, and avarages as calculated by the measure respectively.

 

Issue 1 arrires from the fact that I bound the calculation to a year which of the selected user input. Issue 2 arrises from the values approach.

Mitigating issue 2 by using a startOfWeek date with a datedifference in days does not yield correct results.

 

Can one hint me on the correct approach?

Need Help

1 Reply

  • v-lili6-msft's avatar
    v-lili6-msft
    Icon for Community Support rankCommunity Support
    7 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