cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## 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?

Community Support

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors