Historical Dep Dates Calculation in Power BI

[shansakhi]

Hello All,
I need your guidenace to calculate historical dates based on Current Dep Date and Last Dep Date in Power BI editor.
Below is the sample data where I used excel formula to calculate historical date (community assisted me to create this excel formula).

Explanation:
1 . For Bus No 101 - Current Dep date is 20-Sep (Fri) and Last Dep Date was 18-Sep (Wed) as 18Sep was Wed, I have to take the first Fri before 18Sep i.e. 13Sep.
2. For Bus No 102 - Current Dep Date is 25-Sep (Wed) and Last Dep Date was 11-Aug (Sun) as 11Aug was Sun, I have to take the First Wed before 11-Aug i.e. 07-Sep.
2. For Bus No 103 - Current Dep Date is 31-Dec (Tue) and Last Dep Date was 20-Mar (Wed) as 20Mar was Wed, I have to take the First Tue before 20-Mar i.e. 19-Mar.

Book1 (006).xlsx
	A	B	C	D	E	F
1	Bus No	Current Dep Date	Current Day	Last Dep Date	Flown Date	Flown Day
2	101	20-Sep-24	Friday	18-Sep-24	13-Sep-24	Friday
3	102	25-Sep-24	Wednesday	11-Aug-24	7-Aug-24	Wednesday
4	103	31-Dec-24	Tuesday	20-Mar-24	19-Mar-24	Tuesday
Sheet2

Cell Formulas
Range	Formula
E2:E4	E2	=B2-MAX(0,ROUNDUP((B2-D2)/7,0)*7)
F2:F4,C2:C4	F2	=TEXT(E2,"DDDD")

Regards,

 

[shansakhi]

=[Current Dep Date]-List.Max(0,Number.RoundUp(([Current Dep Date]-[Last Dep Date])/7,0)*7)

I am using above formula in Add column - Custom Column but result is Error

Regards,

 

[shansakhi]

Hi all,
May I request assistance for this.