Forum Discussion

Dyke211's avatar
Dyke211
Icon for Helper I rankHelper I
2 years ago

Calulcuating Date difference in days within same column

Hello Guys, i want to get the number of days between the dates within same column in DAX. FOR INSTANCE.. wOULD LIKE TO REPLICATE THIS IN POWER biwHAT I WANTwHAT I HAVE

5 Replies

  • quantumudit's avatar
    quantumudit
    Icon for Super User rankSuper User
    2 years ago

    Hi Dyke211 
    You can use the following DAX formula to create the calculated column in the table:

    Days = 
VAR _currentDate = Assets[Date]
VAR _previousDate =
    CALCULATE (
        LASTDATE ( Assets[Date] ),
        FILTER ( Assets, Assets[Date] < _currentDate )
    )
VAR _days =
    DATEDIFF ( _previousDate, _currentDate, DAY )
RETURN
    IF ( ISBLANK ( _days ), 0, _days )

     

    Here is the snapshot of the solution:

     

     

    Best Regards,
    Udit

    If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
    Appreciate your Kudo 👍

    🚀 Let's Connect: LinkedIn || YouTube || Medium || GitHub
    ✨ Visit My Linktree: LinkTree

    • Dyke211's avatar
      Dyke211
      Icon for Helper I rankHelper I
      2 years ago

      Hi, I just tried your code with the actual data, but got this errorHow this column looks likeThe error i got...

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        As the error states, it looks like your [EVT_OBJECT] column is not of DATE data type, this will need transforming before the measure will work.

  • Dyke211's avatar
    Dyke211
    Icon for Helper I rankHelper I
    2 years ago

    tHE oBJECT ISNT MEANT TO BE A DATE ITS ACTUALLY UNIQUE VALUES OF THINGS

    • quantumudit's avatar
      quantumudit
      Icon for Super User rankSuper User
      2 years ago

      Hi Dyke211 

      Please correct me if I have misunderstood the issue. However, you need a date field to calculate the "days" difference between subsequent rows. If you wish to retain it as a text field, then you must convert it into a date type during the calculation to determine the days difference.

      If that is the case, then simply use DATEVALUE(Assets[Date]) instead.