Forum Discussion
Solved
Calculate delay based on latest received date vs docdate
I have a table with multiple received date (spilt from remark column key in by user), I want to calculate delay day based on latest received date vs docdate.
| Cust | DocDate | Rec Date 1 | Rec Date 2 | Rec Date 3 | Rec Date 4 | Rec Date 5 |
| Cust A | 3/1/2020 | 10/1/2020 | 5/2/2020 | |||
| Cust B | 10/1/2020 | 12/2/2020 | 6/3/2020 | 12/3/2020 | ||
| Cust C | 28/1/2020 | 10/2/2020 | 15/2/2020 | 28/2/2020 | 5/3/2020 | 13/3/2020 |
Any formula i can use to calculate delay day based on latest received date vs docdate?
Thank you.
- You could use Power Query Editor to unpivot all the Rec Dates (Select all the Rec Dates and click Unpivot Columns in the ribbon). Then use a matrix to plot Cust in rows and put the measure in values:
Measure = DATEDIFF(Max(Rec Date 1), Max(DocDate))
2 Replies
- amitchandak
Super User
sglee , In power query you can add a max column
dax
https://bielite.com/blog/calculating-the-max-of-multiple-columns-or-measures-in-power-bi/
max Rec Date = max(Table1[Rec Date1],max(Table1[Rec Date2],max(Table1[Rec Date3],max(Table1[Rec Date4],Table1[Rec Date5]))))
- AllisonKennedy
Community Champion
You could use Power Query Editor to unpivot all the Rec Dates (Select all the Rec Dates and click Unpivot Columns in the ribbon). Then use a matrix to plot Cust in rows and put the measure in values:
Measure = DATEDIFF(Max(Rec Date 1), Max(DocDate))
