topic Calculation of 4 weeks Rolling average based on condition in DAX Commands and Tips

Calculation of 4 weeks Rolling average based on condition

Learner27 — Mon, 10 Jul 2023 20:37:49 GMT

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, Anonymous , Greg_Deckler , Anonymous , v-yueyunzh-msft
Thanx in advance please help me on this one, your help is much needed
Thanks and regards
Learner27

Re: Calculation of 4 weeks Rolling average based on condition

Greg_Deckler — Mon, 10 Jul 2023 23:25:31 GMT

Learner27 See if this helps:

Re: Calculation of 4 weeks Rolling average based on condition

OwenAuger — Tue, 11 Jul 2023 08:04:13 GMT

Learner27

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.

Re: Calculation of 4 weeks Rolling average based on condition

Learner27 — Tue, 11 Jul 2023 09:22:52 GMT

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.

Re: Calculation of 4 weeks Rolling average based on condition

OwenAuger — Wed, 12 Jul 2023 07:24:23 GMT

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, [Hours On Task] ), -- Option 2: General formula DIVIDE ( [R_S_E] + [C_S_E] + [R_Z_ Act Hrs] + [C_Z_Act Hrs], [Hours On Task] ) ) 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

Re: Calculation of 4 weeks Rolling average based on condition

Learner27 — Wed, 12 Jul 2023 09:56:03 GMT

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,

[Hours On Task]
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

Re: Calculation of 4 weeks Rolling average based on condition

OwenAuger — Wed, 12 Jul 2023 11:58:08 GMT

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

Re: Calculation of 4 weeks Rolling average based on condition

Learner27 — Wed, 12 Jul 2023 13:00:59 GMT

Hi OwenAuger

"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

Re: Calculation of 4 weeks Rolling average based on condition

OwenAuger — Fri, 14 Jul 2023 12:53:19 GMT

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], [Hours On Task] ) ), 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, [Hours On Task] ), -- Option 2: General formula DIVIDE ( [R_S_E] + [C_S_E] + [R_Z_ Act Hrs] + [C_Z_Act Hrs], [Hours On Task] ) ) 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

Re: Calculation of 4 weeks Rolling average based on condition

Learner27 — Fri, 14 Jul 2023 13:08:56 GMT

Hi OwenAuger

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