Calculate Day Diff between 2 dates on row level

[Anonymous]

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

[Anonymous · Accepted solution]

HI @Anonymous 

Please check the below code. This gives 1 day as per your need.

CHANGE DATE = DATEDIFF([PHASE],MAXX(FILTER(TABLE,[PRODUCT] = EARLIER(PRODUCT) && [ENTRY] < EARLIER([ENTRY])),[PHASE] ),DAY)

[amitchandak]

@Anonymous , Not very clear

Try like

datediff(maxx(filter(table,[Product] = earlier(Product ) && [Entry] < earlier([Entry])),[Change Date] ),[Change Date],day)

[Anonymous]

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.

[parry2k]

@Anonymous what you are expecting #2 - #1, #3 - #2 

[Anonymous]

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.

[parry2k]

@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]

@parry2k  Thank you.

the function might be correct but we have different Scenario's:

1. Entry No different but Change date is the same

2. Entry No not ascending (1 3 4 6 instead of 1 2 3) which means Entry No -1 won't work properly.

3. Entry No not ascending and date is the same

4. First Entry No row date diff should always be 0 because there is no previous Entry No.

5. Entry No might start at 3 instead of 1.