Forum Discussion
Calculate Day Diff between 2 dates on row level
Hi there,
I hope you can help me!
I have an issue: I used the table below as an example
I am trying to calculate the day difference based on " Change Date " for each Product based on " Entry NO ". Normally if I had 2 date columns that would be easy, but in this case, I have only 1 date column with all change dates.
It's easy to solve this in excel, but how can I solve this in Power BI? it has to be dynamic.
Product Entry NoPhase Change Date
| Bike | 1 | Start | 8/11/2020 |
| Bike | 2 | Develop | 8/12/2020 |
| Bike | 3 | Finish | 8/14/2020 |
| Car | 1 | Start | 8/12/2020 |
| Car | 2 | Develop | 8/15/2020 |
| Motor | 1 | Start | 8/17/2020 |
| Motor | 2 | Develop | 8/20/2020 |
| Motor | 3 | Finish | 8/24/2020 |
| Step | 1 | Start | 8/11/2020 |
| Step | 2 | Develop | 8/20/2020 |
| Step | 3 | Finish | 8/29/2020 |
7 Replies
Anonymous what you are expecting #2 - #1, #3 - #2
What is expect is the difference in days for each product based on entry no and change date.
Example,
Bike has 3 entry no, the difference in days =
Change date entry no 2 - Change date entry no 3 = (12-8-2020) - (11-8-2020) = 1 day.
This should be dynamic where Power BI takes into consideration the Product, Entry No before calculating the diff date in day.
Anonymous try this measure
New Measure = VAR __entry = MAX ( Table[Entry] ) - 1 VAR __prevDate = CALCULATE ( MAX ( Table[ChangeDate] ), Table[Entry] = __entry ) RETURN DATEDIFF ( __prevDate, MAX ( Table[ChangeDate] ), DAY )I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
Anonymous , Not very clear
Try like
datediff(maxx(filter(table,[Product] = earlier(Product ) && [Entry] < earlier([Entry])),[Change Date] ),[Change Date],day)
What is expect is the difference in days for each product based on entry no and change date.
Example,
Bike has 3 entry no, the difference in days =
Change date entry no 2 - Change date entry no 3 = (12-8-2020) - (11-8-2020) = 1 day.
This should be dynamic where Power BI takes into consideration the Product, Entry No before calculating the diff date in days.
