Calculate Last 6 Week Days Average

[shansakhi]

Hello All,
I am trying to get average of last Week Days Average in power Query as measure where if any value to calculate average is zero or not available then to be considered as blank.
Can somebody assist with it.


Regards,
Shan

 

[shansakhi]

Hello All,
Can anybody assist on the query.

 

[AlienSx]

Hello, @shansakhi .
1. Read forum Guidelines - we can't help you w/o sample data and expected output based on that data.
2. You mentioned "measure". It's from DAX universe, not PQ M language. What do you actually need?

 

[shansakhi]

Thank you, Alien for highlighting.
I will come with a few data points.

 

[shansakhi]

Here is the data
ACT DAta and BKD Data are the input data.
from A4 to H6 is Report
I5 to AH6 is backend calculation

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH
2									Backend Calculation >>>>>>>>
3										ACT SF for Same Day based on Day for Same Car No and Route >>>>>													BKD SF for Same Day based on Day for Same Car No and Route >>>>>
4	Date	DOW	Car No	Route	SF	Cond Date	6 Week Average ACT SF for same day	6 Week Average BKD SF for same day	Days to Dep	Week 1 for same day	Week 1 SF	Week 2 for same day	Week 2 SF	Week 3 for same day	Week 3 SF	Week 4 for same day	Week 4 SF	Week 5 for same day	Week 5 SF	Week 6 for same day	Week 6 SF		Week 1 for same day & Days to Dep	Week 1 BKD SF	Week 2 for same day & Days to Dep	Week 2 BKD SF	Week 3 for same day & Days to Dep	Week 3 BKD SF	Week 4 for same day & Days to Dep	Week 4 BKD SF	Week 5 for same day & Days to Dep	Week 5 BKD SF	Week 6 for same day & Days to Dep	Week 6 BKD SF
5	18-Jul-24	Thursday	122	ABCDEF	50%	16-Jul-24	54%	67%	2	11-Jul-24	45%	4-Jul-24	65%	27-Jun-24	85%	20-Jun-24	36%	13-Jun-24	39%	6-Jun-24	65%		454842122ABCDEF	56%	454772122ABCDEF	65%	454702122ABCDEF		454632122ABCDEF	55%	454562122ABCDEF	96%	454492122ABCDEF	65%
6	19-Jul-24	Friday	120	DEFGHF	30%	12-Jul-24	64%	67%	7	12-Jul-24	55%	5-Jul-24		28-Jun-24	43%	21-Jun-24	89%	14-Jun-24	67%	7-Jun-24	76%		454857120DEFGHF	66%	454787120DEFGHF	45%	454717120DEFGHF		454647120DEFGHF	76%	454577120DEFGHF	78%	454507120DEFGHF	70%
7
8
9	ACT Data >>>						BKD Data >>>
10	Data	DOW	Car No	Route	SF		Cond	Day to Dep	As on Date	Data	DOW	Car No	Route	SF
11	11-Jul-24	Thursday	122	ABCDEF	45%		454842122ABCDEF	2	9-Jul-24	11-Jul-24	Thursday	122	ABCDEF	56%
12	10-Jul-24	Wednesday	122	ABCDEF	78%		454772122ABCDEF	2	2-Jul-24	4-Jul-24	Thursday	122	ABCDEF	65%
13	4-Jul-24	Thursday	122	ABCDEF	65%		454632122ABCDEF	2	18-Jun-24	20-Jun-24	Thursday	122	ABCDEF	55%
14	2-Jul-24	Tuesday	122	ABCDEF	97%		454562122ABCDEF	2	11-Jun-24	13-Jun-24	Thursday	122	ABCDEF	86%
15	27-Jun-24	Thursday	122	ABCDEF	85%		454492122ABCDEF	2	4-Jun-24	6-Jun-24	Thursday	122	ABCDEF	65%
16	20-Jun-24	Thursday	122	ABCDEF	36%		454857120DEFGHF	7	5-Jul-24	12-Jul-24	Friday	120	DEFGHF	66%
17	13-Jun-24	Thursday	122	ABCDEF	39%		454787120DEFGHF	7	28-Jun-24	5-Jul-24	Friday	120	DEFGHF	45%
18	6-Jun-24	Thursday	122	ABCDEF	65%		454647120DEFGHF	7	14-Jun-24	21-Jun-24	Friday	120	DEFGHF	76%
19	12-Jul-24	Friday	121	DEFGHF	55%		454577120DEFGHF	7	7-Jun-24	14-Jun-24	Friday	120	DEFGHF	78%
20	28-Jun-24	Friday	121	DEFGHF	43%		454507120DEFGHF	7	31-May-24	7-Jun-24	Friday	120	DEFGHF	70%
21	21-Jun-24	Friday	121	DEFGHF	89%
22	14-Jun-24	Friday	121	DEFGHF	67%
23	7-Jun-24	Friday	121	DEFGHF	76%
Sheet1

Cell Formulas
Range	Formula
G5:G6	G5	=AVERAGE(K5,M5,O5,Q5,S5,V5)
H5:H6	H5	=AVERAGE(X5,Z5,AB5,AD5,AF5,AH5)
I5:I6	I5	=A5-F5
J5:J6	J5	=A5-MAX(0,ROUNDUP((A5-F5)/7,0)*7)
B5:B6	B5	=TEXT(A5,"DDDD")
L5:L6,T5:T6,R5:R6,P5:P6,N5:N6	L5	=J5-7
W5:W6,AG5:AG6,AE5:AE6,AC5:AC6,AA5:AA6,Y5:Y6	W5	=J5&$I5&$C5&$D5