Forum Discussion
Calculate Day Diff between 2 dates on row level
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)
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.⚡
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.