Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 " En...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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)

     

parry2k's avatar
parry2k
Super User
6 years ago

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's avatar
Anonymous
Not applicable
6 years ago

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.