Available Week Data Capture

[shansakhi]

Hello All,
I am trying to build a report where based on day of week level previous available week data is considered as a criteria of Last flown date.
If some one can guide me to build this report in Power BI.
Due to limitation of 3000 cell unable to provide all information..

Forecast_Mr Excel_Template.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
2	Report	Last Flown Date	7-Jul-24
3	Bus No	Routing	Class	Dep Date	Day	BKD SF	Expected SF based on 6 Weeks	Expected based on 13 Weeks	Expected SF based on 21 Weeks	Average - Flown Week 1 SF to Week 6 SF	Average - FLown Week 1 SF to Week 13 SF	Average - Flown Week 1 SF to Week 21 SF	Average - Booked Week 1 SF to Week 6 SF	Average - Booked Week 1 SF to Week 13 SF	Average - Booked Week 1 SF to Week 21 SF
4	101	LON-ZRH	Y	10-Jul-24	Wed	56%	80%	86%	91%	72%	72%	75%	48%	42%	40%
5	101	LON-ZRH	J	10-Jul-24	Wed	65%	88%	93%	96%	67%	67%	70%	44%	39%	38%
6	103	ZRH-PAR	Y	15-Jul-24	Mon	53%	64%	70%	70%	60%	60%	58%	49%	43%	41%
7	103	ZRH-PAR	J	15-Jul-24	Mon	59%	66%	73%	79%	51%	52%	57%	44%	38%	36%
8
9	Flown Data set								Booked Data set
10	Cond	Bus No	Routing	Class	Dep Date	Day	Flown SF		Cond	Days before Dep	As on date	Bus No	Routing	Class	Dep Date	Day	Booked SF
11	101LON-ZRHY45441	101	LON-ZRH	Y	29-May-24	Wed	77%		454763101LON-ZRHY	3	30-Jun-24	101	LON-ZRH	Y	3-Jul-24	Wed	70%
12	101LON-ZRHY45448	101	LON-ZRH	Y	5-Jun-24	Wed	84%		454693101LON-ZRHY	3	23-Jun-24	101	LON-ZRH	Y	26-Jun-24	Wed	48%
13	101LON-ZRHY45455	101	LON-ZRH	Y	12-Jun-24	Wed	91%		454623101LON-ZRHY	3	16-Jun-24	101	LON-ZRH	Y	19-Jun-24	Wed	46%
14	101LON-ZRHY45462	101	LON-ZRH	Y	19-Jun-24	Wed	98%		454553101LON-ZRHY	3	9-Jun-24	101	LON-ZRH	Y	12-Jun-24	Wed	44%
15	101LON-ZRHY45469	101	LON-ZRH	Y	26-Jun-24	Wed	37%		454483101LON-ZRHY	3	2-Jun-24	101	LON-ZRH	Y	5-Jun-24	Wed	42%
16	101LON-ZRHY45476	101	LON-ZRH	Y	3-Jul-24	Wed	44%		454748103ZRH-PARY	8	23-Jun-24	103	ZRH-PAR	Y	1-Jul-24	Mon	71%
17	101LON-ZRHJ45441	101	LON-ZRH	J	29-May-24	Wed	72%		454608103ZRH-PARY	8	9-Jun-24	103	ZRH-PAR	Y	17-Jun-24	Mon	49%
18	101LON-ZRHJ45448	101	LON-ZRH	J	5-Jun-24	Wed	79%		454468103ZRH-PARY	8	26-May-24	103	ZRH-PAR	Y	3-Jun-24	Mon	47%
19	101LON-ZRHJ45455	101	LON-ZRH	J	12-Jun-24	Wed	86%		454328103ZRH-PARY	8	12-May-24	103	ZRH-PAR	Y	20-May-24	Mon	45%
20	101LON-ZRHJ45462	101	LON-ZRH	J	19-Jun-24	Wed	93%		454188103ZRH-PARY	8	28-Apr-24	103	ZRH-PAR	Y	6-May-24	Mon	43%
21	101LON-ZRHJ45469	101	LON-ZRH	J	26-Jun-24	Wed	32%		454763101LON-ZRHJ	3	30-Jun-24	101	LON-ZRH	J	3-Jul-24	Wed	65%
22	101LON-ZRHJ45476	101	LON-ZRH	J	3-Jul-24	Wed	39%		454693101LON-ZRHJ	3	23-Jun-24	101	LON-ZRH	J	26-Jun-24	Wed	43%
23	103ZRH-PARY45404	103	ZRH-PAR	Y	22-Apr-24	Mon	31%		454623101LON-ZRHJ	3	16-Jun-24	101	LON-ZRH	J	19-Jun-24	Wed	41%
24	103ZRH-PARY45418	103	ZRH-PAR	Y	6-May-24	Mon	45%		454553101LON-ZRHJ	3	9-Jun-24	101	LON-ZRH	J	12-Jun-24	Wed	39%
25	103ZRH-PARY45432	103	ZRH-PAR	Y	20-May-24	Mon	59%		454483101LON-ZRHJ	3	2-Jun-24	101	LON-ZRH	J	5-Jun-24	Wed	37%
26	103ZRH-PARY45446	103	ZRH-PAR	Y	3-Jun-24	Mon	73%		454748103ZRH-PARJ	8	23-Jun-24	103	ZRH-PAR	J	1-Jul-24	Mon	66%
27	103ZRH-PARY45460	103	ZRH-PAR	Y	17-Jun-24	Mon	87%		454608103ZRH-PARJ	8	9-Jun-24	103	ZRH-PAR	J	17-Jun-24	Mon	44%
28	103ZRH-PARY45474	103	ZRH-PAR	Y	1-Jul-24	Mon	64%		454468103ZRH-PARJ	8	26-May-24	103	ZRH-PAR	J	3-Jun-24	Mon	42%
29	103ZRH-PARJ45404	103	ZRH-PAR	J	22-Apr-24	Mon	34%		454328103ZRH-PARJ	8	12-May-24	103	ZRH-PAR	J	20-May-24	Mon	40%
30	103ZRH-PARJ45418	103	ZRH-PAR	J	6-May-24	Mon	48%		454188103ZRH-PARJ	8	28-Apr-24	103	ZRH-PAR	J	6-May-24	Mon	38%
31	103ZRH-PARJ45432	103	ZRH-PAR	J	20-May-24	Mon	62%
32	103ZRH-PARJ45446	103	ZRH-PAR	J	3-Jun-24	Mon	76%
33	103ZRH-PARJ45460	103	ZRH-PAR	J	17-Jun-24	Mon	22%
34	103ZRH-PARJ45474	103	ZRH-PAR	J	1-Jul-24	Mon	63%
35
36	Back End Calculation
37	FLOWN SF											BOOKED SF
38	Week 1	Week 1 SF	Week 2	Week 2 SF	Week 3	Week 3 SF	Week 4	Week 4 SF	Week 5	Week 5 SF	Days Before Departure	Week 1	Week 1 SF	Week 2	Week 2 SF	Week 3	Week 3 SF	Week 4	Week 4 SF	Week 5	Week 5 SF
39	3-Jul-24	44%	26-Jun-24	37%	19-Jun-24	98%	12-Jun-24	91%	5-Jun-24	84%	3	454763101LON-ZRHY	70%	454693101LON-ZRHY	48%	454623101LON-ZRHY	46%	454553101LON-ZRHY	44%	454483101LON-ZRHY	42%
40	3-Jul-24	39%	26-Jun-24	32%	19-Jun-24	93%	12-Jun-24	86%	5-Jun-24	79%	3	454763101LON-ZRHJ	65%	454693101LON-ZRHJ	43%	454623101LON-ZRHJ	41%	454553101LON-ZRHJ	39%	454483101LON-ZRHJ	37%
41	1-Jul-24	64%	17-Jun-24	87%	3-Jun-24	73%	20-May-24	59%	6-May-24	45%	8	454748103ZRH-PARY	71%	454608103ZRH-PARY	49%	454468103ZRH-PARY	47%	454328103ZRH-PARY	45%	454188103ZRH-PARY	43%
42	1-Jul-24	63%	17-Jun-24	22%	3-Jun-24	76%	20-May-24	62%	6-May-24	48%	8	454748103ZRH-PARJ	66%	454608103ZRH-PARJ	44%	454468103ZRH-PARJ	42%	454328103ZRH-PARJ	40%	454188103ZRH-PARJ	38%
Sheet1

Cell Formulas
Range	Formula
H4:J4,I5:J7	H4	=$G4+(K4-N4)
H5:H7	H5	=G5+(K5-N5)
Q11:Q30,F4:F7	F4	=TEXT(E4,"DDD")
G6	G6	=G4-3%
G7	G7	=G5-6%
J11:J30	J11	=P11&K11&M11&N11&O11
K11:K30	K11	=P11-L11
K39:K42,I39:I42,G39:G42,E39:E42,C39:C42	K39	=VLOOKUP(($B4&$C4&$D4&J39),$B$10:$H$595,7,FALSE)
L39:L42	L39	=E4-$D$2
M39:M42,O39:O42,Q39:Q42,S39:S42,U39:U42	M39	=B39&$L39&$B4&$C4&$D4
N39:N42,P39:P42,R39:R42,T39:T42,V39:V42	N39	=VLOOKUP(M39,$J$10:$R$30,9,FALSE)