Conditional Dynamic DAX to calculate Avg of recent 2 weeks or 1 week before &after from audit week

[Anonymous]

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.

[amitchandak]

@Anonymous , 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-Last-Week/ba-p/1051123

[Anonymous]

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

[Greg_Deckler]

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