Calculation of 4 weeks Rolling average based on condition

[Learner27]

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

[OwenAuger]

@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.

[Learner27]

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.

[OwenAuger]

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

[Learner27]

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

[OwenAuger]

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

[Learner27]

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

[OwenAuger · Accepted solution]

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

[Learner27]

Hi @OwenAuger 

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

[Greg_Deckler]

@Learner27 See if this helps: