Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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...
User | Count |
---|---|
48 | |
44 | |
19 | |
14 | |
14 |
User | Count |
---|---|
107 | |
55 | |
28 | |
19 | |
14 |