Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Learner27
Helper III
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

Learner27_0-1689020244698.png

@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



1 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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

9 REPLIES 9
OwenAuger
Super User
Super User

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

 

 


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

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.

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

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

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)

OwenAuger_0-1689163014673.png

Regards


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

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

 



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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Hi @OwenAuger 

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

Greg_Deckler
Super User
Super User

@Learner27 See if this helps:



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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

Top Solution Authors