Find the date difference for a single column

[data23f]

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!

[Icey]

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.

[amitchandak]

@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]

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]

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.

[HotChilli]

Can you clarify this please "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."

What is in the slicer?

If you post some sample data (not a picture) and desired outcome ,someone will help you out.

[data23f]

I'm trying to attach a sample power bi .pbix file but I don't see the option to.

How do I attach a file?

[HotChilli]

Dropbox or similar and post the link

[data23f]

@HotChilli  I don't want to share a folder which perhaps can have confidential information. So instead, I'm sticking with the screenshot. The data is very simple.

This is done in Excel and in the Output field it's calculating the # of days difference from the previous date.

The slicer just had a way of segmenting the data from Country, Region. But I should be able to incorporate this once the new column is calculated.

Hope this helps.

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[HotChilli]

A column for the simple example

ColumnA = VAR _date = TableCats[Date]
VAR _prevDate = CALCULATE(MAX(TableCats[Date]), FILTER(TableCats, TableCats[Date] < _date))
RETURN
   DATEDIFF(_prevDate, _date ,  DAY)

For future posts, please post data directly (you can paste it into the post) or anonymise the data and link a pbix