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.
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 | ||||||
---|---|---|---|---|---|---|
A | B | C | D | E | F | |
Sheet2 | ||||||
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 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
E2:E4 | E2 | =B2-MAX(0,ROUNDUP((B2-D2)/7,0)*7) |
F2:F4,C2:C4 | F2 | =TEXT(E2,"DDDD") |