Forum Discussion

data23f's avatar
data23f
Helper I
6 years ago
Solved

Find the date difference for a single column

Hi,

 

I have a Date field in a table.

 

 

 

 

 

 

My goal is to create a new column that calculates the date difference from the previous date in # of days.

For example:

• 1/17/20 and 1/28/20 the difference is 11 days.

• 1/28/20 and 1/30/20 the difference is 2 days.

• 1/30/20 and 2/3/20 the difference is 4 days.

• etc...

 

Also, this column needs to be dynamic meaning if a value is selected in a slicer, then this new column should reflect the new calculation difference.

 

Let me know if you have any questions and thanks in advance!

Need Help

10 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    data23f 

    Try a new column like

    Avg  = datediff(maxx(filter(table,table[date] <earlier(table[date])),table[date]),table[date],day)

     

    you may have add or subtract 1.

    • data23f's avatar
      data23f
      Helper I
      6 years ago

      Thanks Amit for your response.

       

      I did try your DAX as a new column and changed data type to date.

       

      and when inserted into the table I got the following:

       

      All the values are the same. Any ideas how to fix this?

      Thank you.

      • data23f's avatar
        data23f
        Helper I
        6 years ago

        Can someone please assist?


        I have tried searching on the Community, on YouTube, and online, but I can't get this to work.


        Probably 20 different formulas I've used and I still can't get this date difference to work. I've tried creating a separate column and then doing a date difference, but still nothing.

  • Icey's avatar
    Icey
    Community Support
    6 years ago

    Hi data23f ,

     

    A calculated column can't change automatically, but a measure can. So, you need to create a measure, not a column. Please check whether Ashish_Mathur 's measure works.

     

     

    Best Regards,

    Icey

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.