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

Helper III

## Calculation of 4 weeks Rolling average based on condition

Hi everyone ,
I'm trying to create a measure for a rolling 4-week average. Here's how the calculation should be done:

If the technician's name is present in the previous 4 weeks, the calculation is as follows:
(R_S_E + C_S_E + R_Z_Act Hrs * Previous Weeks Avg + C_Z_Act Hrs * Previous Weeks Avg) / Hours on Task.

If the technician is not present in any of the weeks in the previous 4 weeks, we will follow a general formula:
(R_S_E + C_S_E + R_Z_Act Hrs + C_Z_Act Hrs) / Hours on Task.

All of these calculations should be included in a single measure. I have also attached a sample Pbix file and a snapshot of the desired result that I would like to see.

Sample Data Zero Dollar Work.pbix

@tamerj1
@v-jianboli-msft
@johnt75
@lbendlin , @Jihwan_Kim   , @v-yueyunzh-msft@v-tangjie-msft , @Greg_Deckler , @v-rzhou-msft , @v-yueyunzh-msft
Thanks and regards
Learner27

1 ACCEPTED SOLUTION
Super User

Thanks for the further detail @Learner27 🙂

I have attached my current working PBIX.

I think the closest I can get to what you want is below.

A slight issue is that DAX cannot work recursively.

So we can calculate:

• Average (Week 5) = Average of weeks 1-4 (actual data)

But we can't then recursively calculate

• Average (Week 6) = Average of weeks 2-4 (actual data) & Average (Week 5)

That is, the moving average can look at previous 4 weeks of actual data, but it can't incorporate the average for a previous week in a later week's average.

In the below code, PreviousWeeksAverage is calculated over the previous 4 weeks of actual data (where data exists):

``````Zero Rate Dollar Work =
IF (
HASONEVALUE ( 'Calendar'[start date] ) -- single week filtered
&& HASONEVALUE ( ST_PFP_Report[AssignedTechnicians] ), -- single Technician filtered
-- Get the previous 4 weeks (before the single filtered week)
VAR PreviousFourWeeks =
WINDOW ( -1, REL, -4, REL, ALL ( 'Calendar'[start date] ) )

-- Determine if the current Technician
-- appeared in the previous 4 weeks
VAR ExistsInPreviousFourWeeks =
CALCULATE (
NOT ISEMPTY ( ST_PFP_Report ),
ALL ( 'Calendar' ), -- clear 'Calendar' filters (technically not needed as 'Calendar' is in expanded ST_PFP_Report table)
PreviousFourWeeks -- filter on Previous 4 weeks
)
VAR Result =
IF (
ExistsInPreviousFourWeeks, -- True/False
-- Option 1: Calculation based on previous 4 weeks
VAR PreviousWeeksAvg =
CALCULATE (

-- This AVERAGEX expression averages (R_S_E + C_S_E + R_Z_act hrs + C_Z_Act Hrs) / Hours on task
-- over the previous 4 weeks.
AVERAGEX (
VALUES ( 'Calendar'[start date] ),
DIVIDE (
[R_S_E] + [C_S_E] + [R_Z_ Act Hrs] + [C_Z_Act Hrs],
)
),

ALL ( 'Calendar' ), -- clear 'Calendar' filters (technically not needed as 'Calendar' is in expanded ST_PFP_Report table)
PreviousFourWeeks -- filter on Previous 4 weeks
)
RETURN
DIVIDE (
[R_S_E] + [C_S_E] + [R_Z_ Act Hrs] * PreviousWeeksAvg
+ [C_Z_Act Hrs] * PreviousWeeksAvg,
),
-- Option 2: General formula
DIVIDE (
[R_S_E] + [C_S_E] + [R_Z_ Act Hrs] + [C_Z_Act Hrs],
)
)
RETURN
Result
)``````

That's about the best I can do at the moment. If it's not quite right, I hope you can modify it appropriately.

All the best,

Owen

Owen Auger
Blog
9 REPLIES 9
Super User

Could you provide a worked example of the calculation, for both cases (technician present and technician not present in prior 4 weeks)?

It's not clear what Previous Weeks Avg refers to.

Owen Auger
Blog
Helper III

Hi @OwenAuger
Thank you for Quick response,
I have shared you the excel file in your Inbox please let me know if  any further information is required.

Super User

Hi again @Learner27

I have had a look at the PBIX/Excel file (got a bit busy since yesterday). Thanks for providing those 🙂

I have suggested below some DAX code for the Zero Rate Dollar Work measure that should be structurally what you need, but is missing the expression for "Previous Weeks Avg". Could you try to populate that?

Edited PBIX is attached.

Notes on the measure:

• I have assumed that the measure should only be evaluated for a single Week and AssignedTechnician. This is the reason for the HASONEVALUE tests at the start.
• If the above assumption is incorrect, we would need some logic for aggregating across Technicians and weeks.
• The ExistsInPreviousFourWeeks variable returns True if the current technician appeared in the previous 4 weeks in ST_PFP_Report table (ignoring any other filters that might be present).
• The PreviousWeeksAvg variable is set to 1 (calculated over previous 4 weeks) as I didn't follow how it should be calculated sorry. You can replace this with the correct expression.

``````Zero Rate Dollar Work =
IF (
HASONEVALUE ( 'Calendar'[start date] ) -- single week filtered
&& HASONEVALUE ( ST_PFP_Report[AssignedTechnicians] ), -- single Technician filtered
-- Get the previous 4 weeks (before the single filtered week)
VAR PreviousFourWeeks =
WINDOW ( -1, REL, -4, REL, ALL ( 'Calendar'[start date] ) )

-- Determine if the current Technician
-- appeared in the previous 4 weeks
VAR ExistsInPreviousFourWeeks =
CALCULATE (
NOT ISEMPTY ( ST_PFP_Report ),
ALL ( 'Calendar' ), -- clear 'Calendar' filters (technically not needed as 'Calendar' is in expanded ST_PFP_Report table)
PreviousFourWeeks -- filter on Previous 4 weeks
)
VAR Result =
IF (
ExistsInPreviousFourWeeks, -- True/False
-- Option 1: Calculation based on previous 4 weeks
VAR PreviousWeeksAvg =
CALCULATE (
1, -- *** replace this with correct expression ***
ALL ( 'Calendar' ), -- clear 'Calendar' filters (technically not needed as 'Calendar' is in expanded ST_PFP_Report table)
PreviousFourWeeks -- filter on Previous 4 weeks
)
RETURN
DIVIDE (
[R_S_E] + [C_S_E] + [R_Z_ Act Hrs] * PreviousWeeksAvg
+ [C_Z_Act Hrs] * PreviousWeeksAvg,
),
-- Option 2: General formula
DIVIDE (
[R_S_E] + [C_S_E] + [R_Z_ Act Hrs] + [C_Z_Act Hrs],
)
)
RETURN
Result
)``````

There could well be issues as I don't have a full understanding of the background to the calculation, so please treat this as guidance 🙂

Regards,

Owen

Owen Auger
Blog
Helper III

Hi @OwenAuger

Thank you for the measure  but there is still some missing part in the measure.
The part where we use previous weeks average, it is upto calculation multiplying with previous 4 weeks average and in this 4 weeks average it consists of below calculation :

[R_S_E] + [C_S_E] + [R_Z_ Act Hrs] * PreviousWeeksAvg
+ [C_Z_Act Hrs] * PreviousWeeksAvg,
Basically it is like rolling 4 weeks average for the next week for each week
I tried to figure out rolling 4 week average  for the previous week but it is breaking our measure
Super User

Thanks for the feedback - yes, this is the part of the measure where I confess I didn't fully understand the requirements.

I think it might be easiest to take an example from the Power BI dataset.

If we look at Andrew Dusett as at 2022 week 10, could you show me would be the correct calculation for "previous 4 weeks average"?

I have created a page to show his values by week (attached)

Regards

Owen Auger
Blog
Helper III

"Thank you so much for putting in the effort to help me.
I truly appreciate it. I am new to window functions in Power BI, and thanks to you, I have learned about them. Once again, a big thank you to you."

"The four-week average should be calculated as follows:"

calculation would be

1st week = (R_S_E(3511.96)+C_S_E(0.00)+R_Z_act hrs(9.02)+ C_Z_Act Hrs(0.00)) / Hours on task(74.08)=47.53

2nd week = (R_S_E(299.8)+C_S_E(0.00)+R_Z_act hrs(17.51)+ C_Z_Act Hrs(0.00)) / Hours on task(74.97)=40.23

3rd week = (R_S_E(3579.41)+C_S_E(0.00)+R_Z_act hrs(0.00)+ C_Z_Act Hrs(0.00)) / Hours on task(108.36)=33.03

4th week = (R_S_E(242.89)+C_S_E(0.00)+R_Z_act hrs(0.00)+ C_Z_Act Hrs(0.00)) / Hours on task(32.75)=7.42

_______________

5th week Average Wolud be previous 4 weeks average sum =(47.53+40.23+33.03+7.42 )/4 i.e = 32.05

6th week = week2+week3+week4+week5 =(40.23+33.03+7.42+32.05)/4 = 28.18

like this process should continue

if we are in 2023 week 1 would be as

= 2022 week49 +2022 week50+2022 week 51+2022 week 52

Super User

Thanks for the further detail @Learner27 🙂

I have attached my current working PBIX.

I think the closest I can get to what you want is below.

A slight issue is that DAX cannot work recursively.

So we can calculate:

• Average (Week 5) = Average of weeks 1-4 (actual data)

But we can't then recursively calculate

• Average (Week 6) = Average of weeks 2-4 (actual data) & Average (Week 5)

That is, the moving average can look at previous 4 weeks of actual data, but it can't incorporate the average for a previous week in a later week's average.

In the below code, PreviousWeeksAverage is calculated over the previous 4 weeks of actual data (where data exists):

``````Zero Rate Dollar Work =
IF (
HASONEVALUE ( 'Calendar'[start date] ) -- single week filtered
&& HASONEVALUE ( ST_PFP_Report[AssignedTechnicians] ), -- single Technician filtered
-- Get the previous 4 weeks (before the single filtered week)
VAR PreviousFourWeeks =
WINDOW ( -1, REL, -4, REL, ALL ( 'Calendar'[start date] ) )

-- Determine if the current Technician
-- appeared in the previous 4 weeks
VAR ExistsInPreviousFourWeeks =
CALCULATE (
NOT ISEMPTY ( ST_PFP_Report ),
ALL ( 'Calendar' ), -- clear 'Calendar' filters (technically not needed as 'Calendar' is in expanded ST_PFP_Report table)
PreviousFourWeeks -- filter on Previous 4 weeks
)
VAR Result =
IF (
ExistsInPreviousFourWeeks, -- True/False
-- Option 1: Calculation based on previous 4 weeks
VAR PreviousWeeksAvg =
CALCULATE (

-- This AVERAGEX expression averages (R_S_E + C_S_E + R_Z_act hrs + C_Z_Act Hrs) / Hours on task
-- over the previous 4 weeks.
AVERAGEX (
VALUES ( 'Calendar'[start date] ),
DIVIDE (
[R_S_E] + [C_S_E] + [R_Z_ Act Hrs] + [C_Z_Act Hrs],
)
),

ALL ( 'Calendar' ), -- clear 'Calendar' filters (technically not needed as 'Calendar' is in expanded ST_PFP_Report table)
PreviousFourWeeks -- filter on Previous 4 weeks
)
RETURN
DIVIDE (
[R_S_E] + [C_S_E] + [R_Z_ Act Hrs] * PreviousWeeksAvg
+ [C_Z_Act Hrs] * PreviousWeeksAvg,
),
-- Option 2: General formula
DIVIDE (
[R_S_E] + [C_S_E] + [R_Z_ Act Hrs] + [C_Z_Act Hrs],
)
)
RETURN
Result
)``````

That's about the best I can do at the moment. If it's not quite right, I hope you can modify it appropriately.

All the best,

Owen

Owen Auger
Blog
Helper III

Hi @OwenAuger

Thank you for the solution you were like oasis in the desert that had solved my issue,
Thanks a tones!

Super User

@Learner27 See if this helps:

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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 - June 2024

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

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors