Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Dynamic DAX to calculate average of recent 2 weeks before and after from audit dates and if only 1 week data is available than consider in the calculation AND FLAG all conditions in the FINAL OUT WITH THE CONDITIONS COMMENTS mentioned below
Audit data (Table 1)
Emp id | Audit Week start |
1 | 5/31/2020 |
2 | 5/31/2020 |
3 | 5/31/2020 |
4 | 5/31/2020 |
5 | 5/31/2020 |
6 | 5/31/2020 |
7 | 5/31/2020 |
Production Score ( Table 2)
Emp id | Score Week start |
1 | 5/17/2020 |
1 | 5/24/2020 |
1 | 5/31/2020 |
1 | 6/7/2020 |
1 | 6/14/2020 |
2 | 5/3/2020 |
2 | 5/24/2020 |
2 | 5/31/2020 |
2 | 6/14/2020 |
2 | 6/21/2020 |
3 | 5/31/2020 |
3 | 6/14/2020 |
3 | 6/21/2020 |
4 | 5/17/2020 |
4 | 5/24/2020 |
4 | 5/31/2020 |
5 | 5/17/2020 |
5 | 5/24/2020 |
5 | 5/31/2020 |
5 | 6/14/2020 |
6 | 5/17/2020 |
6 | 5/31/2020 |
6 | 6/14/2020 |
6 | 6/21/2020 |
7 | 5/24/2020 |
7 | 5/31/2020 |
7 | 6/14/2020 |
Conditions :-
Condition -1 ->In orginal data both tables are NOT sorted, hence please CONSIDER this in the calculation if required & EXCLUDE Audit weeks while calculating before and after
Condition-2 (Idle Situation)-> (Employee-1) -> From feedback date it should take average values of recent 2 weeks/ # Flag -> "Include- Recent 2 Weeks"
Condition-3 (Employee-2) -> Incase last data is missing for last 2 weeks or 1 week, formula should search untill it finds values for 2 weeks for both before & after /# Flag -> "Include- Others 2 Week"
Condition-4 (Employee-3&4) -> Incase data is missing for before ,after or Both it should be excluded from the calculation/# Flag- Exclude- Missing weeks before" / # Flag- Exclude- Missing weeks after"/ # Flag- Exclude- Missing weeks"
Condition-5 (Employee-5&6) -> Incase data is only available for 1 week than consider it as before and after/# Flag- "Include -1 Week After" / # Flag- "Include -1 Week Before"
Condition-6 (Employee-7) -> Incase data is only available for 1 week for both than consider it as before and after/# Flag- "Include -Both 1 Week Before & After"
Final Output required columns
Reference COLUMN for formula | Emp id | Audit week start | Before dates | After dates | Avg Before Score | Avg After Score | Conditions Flags ( |
Condition-2 | 1 | 5/31/2020 | 5/24/2020 & 5/17/2020 | 6/14/2020 & 6/07/2020 | 200 | 200 | Include- Recent 2 Weeks |
Condition-3 | 2 | 5/31/2020 | 5/3/2020 & 5/24/2020 | 6/21/2020 & 6/14/2020 | 200 | 200 | Include- Others 2 Weeks |
Condition-4 | 3 | 5/31/2020 | - | 6/21/2020 & 6/14/2020 | 0 | 200 | Exclude- Missing weeks Before |
Condition-4 | 4 | 5/31/2020 | 5/24/2020 & 5/17/2020 | - | 200 | 0 | Exclude- Missing weeks After |
Condition-5 | 5 | 5/31/2020 | 5/24/2020 & 5/17/2020 | 6/14/2020 | 200 | 200 | Include -1 Week After |
Condition-5 | 6 | 5/31/2020 | 5/17/2020 | 6/21/2020 & 6/14/2020 | 200 | 200 | Include -1 Week Before |
Condition-6 | 7 | 5/31/2020 | 5/24/2020 | 6/14/2020 | 200 | 200 | Include -Both 1 Week Before & After |
Thanks in advance.
@mbl , refer these two can help
Power BI — WTD Questions— Time Intelligence 4–5
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
@amitchandak Thanks these are super helpful
Now my new challenge is
I need before and after average scores of THOSE EMPLOYEES WHO WERE AUDITED not for all
The DAX should try to find values of 2 weeks before 6 weeks & after 6 weeks of audit week.Please note there is possibiity that only 1 week data is available for some employees.
And provides a measure Avg score BEFORE & Avg score AFTER ...
Audit data (Table 1) | |
emp_id | Audit Week start |
1 | 5/31/2020 |
Production Score ( Table 2) | ||
emp_id | Score Week start | Score |
1 | 5/17/2020 | 59 |
1 | 5/24/2020 | 0 |
2 | 5/24/2020 | 91 |
1 | 6/7/2020 | 31 |
1 | 6/14/2020 | 61 |
Please assist 🙂
See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
23 | |
20 | |
15 | |
13 |
User | Count |
---|---|
51 | |
42 | |
39 | |
19 | |
19 |