Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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
Solved! Go to 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:
But we can't then recursively calculate
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
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.
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:
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
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 :
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
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:
But we can't then recursively calculate
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
Hi @OwenAuger
Thank you for the solution you were like oasis in the desert that had solved my issue,
Thanks a tones!
@Learner27 See if this helps:
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
31 | |
16 | |
14 | |
14 | |
9 |