• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Historical Date Calculation Power BI editor

ShanShami

Member
Hi All,
This is a cross reference query. This query was raised on Mr Excel a few day back.
https://www.mrexcel.com/board/threa...calculation-in-power-bi.1264608/#post-6216773

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
ABCDEF
Sheet2
1Bus NoCurrent Dep DateCurrent DayLast Dep DateFlown DateFlown Day
210120-Sep-24Friday18-Sep-2413-Sep-24Friday
310225-Sep-24Wednesday11-Aug-247-Aug-24Wednesday
410331-Dec-24Tuesday20-Mar-2419-Mar-24Tuesday
Cell Formulas
RangeFormula
E2:E4E2=B2-MAX(0,ROUNDUP((B2-D2)/7,0)*7)
F2:F4,C2:C4F2=TEXT(E2,"DDDD")
 
Try:
Code:
= Date.From(Number.From([Current Dep Date])-List.Max({0,Number.RoundUp((Number.From([Current Dep Date])-Number.From([Last Dep Date]))/7,0)*7}))
or the full MCode to add a column and assign a type to it:
Code:
= Table.AddColumn(#"Changed Type", "Custom", each Date.From(Number.From([Current Dep Date])-List.Max({0,Number.RoundUp((Number.From([Current Dep Date])-Number.From([Last Dep Date]))/7,0)*7})),type date)
 
Back
Top