Forum Discussion

POSPOS's avatar
POSPOS
Post Partisan
2 years ago
Solved

Days between dates of same column using DAX

Hi All, I have a requirement to find the days between dates of the same column.  The first value should be the difference between the date and the date of the next step and the last value should be...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi POSPOS ,

    Please try:

    Column = 
VAR _cur_protocol = 'export'[Protocol Number]
VAR _cur_index = 'export'[Index]
VAR _next_index = CALCULATE(MIN('export'[Index]),FILTER(ALL('export'),'export'[Protocol Number]=_cur_protocol && 'export'[Index]>_cur_index))
VAR _next_date = CALCULATE(MIN('export'[Status Date]),ALLEXCEPT('export','export'[Index]),'export'[Index]=_next_index)
VAR _result = COALESCE(DATEDIFF('export'[Status Date],_next_date,DAY),0)
RETURN
_result

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

Data-estDog's avatar
Data-estDog
Resolver II
2 years ago

You would need a more granular date/time stamp. Or a sequence number (order the status was changed in) in order to achieve that.

Data-estDog's avatar
Data-estDog
Resolver II
2 years ago

If you want to assume status always happens in alphabetical order: 

DaysTillNextStatus =
VAR CurrentStatusDate = Min('export'[Status Date])
VAR CurrentProtocol = SELECTEDVALUE('export'[Protocol Number])
VAR CurrentStatus = SELECTEDVALUE('export'[Status])
var NextDate = CALCULATE(
    Min('export'[Status Date]),
    FILTER(
        ALL('export'),
        ('export'[Status Date] > CurrentStatusDate && CurrentProtocol = 'export'[Protocol Number])
    )
)
var NextDate2 = CALCULATE(
    Min('export'[Status Date]),
    FILTER(
        ALL('export'),
        ( 'export'[Status Date] = CurrentStatusDate
            && CurrentProtocol = 'export'[Protocol Number]
            && 'export'[Status] > CurrentStatus)
    )
)
VAR vDifference = DATEDIFF(CurrentStatusDate , NextDate, DAY)
VAR vDifference2 = DATEDIFF(CurrentStatusDate , NextDate2, DAY)

RETURN IF(ISBLANK(COALESCE(vDifference2, vDifference)), 0, COALESCE(vDifference2, vDifference))