Calculate Datediff if the dates are continuous

[Mohan128256]

Hello All,

I have below dates,

Snapshot Date
4-16-2024
4-11-2024
4-10-2024
4-8-2024
4-5-2024
4-4-2024
4-3-2024
3-27-2024
3-22-2024
3-21-2024
3-20-2024
3-19-2024
3-18-2024

Here i would like to calcaulte the date diffierence with the previous date values if the dates are continous otherwise the calculation should start over.

The result that i am expecting is as follows.

Any help with dax please.

Thanks,

Mohan V.

[Mohan128256]

@v-nuoc-msft thanks for the reply.

the result which i am expecting is not what you have got from the solution which you given here.

Here is the result which i am expecting where I should have the datediff with incremental 1 till it is continious.

If the dates are not continous then it should start over.

Expected result.

Give output:

Please help.

Thanks,

Mohan V.

[v-nuoc-msft]

Hi @Mohan128256 

For your question, here is the method I provided:

Here's some dummy data

"Table"

First, click Transform data to enter the Power Query interface and add a index column to the table.

Create a measure. When the current date is consecutive, the difference between the current date and the previous date is calculated.

Date Difference = 
VAR CurrentDate = SELECTEDVALUE('Table'[Snapshot Date])
VAR PreviousDate = CALCULATE(MAX('Table'[Snapshot Date]), FILTER(ALL('Table'), 'Table'[Snapshot Date] < CurrentDate))
var _date = CALCULATE(MAX('Table'[Snapshot Date]), FILTER(ALL('Table'), 'Table'[Index] = MAX('Table'[Index]) -1 ))
RETURN
IF(DATEDIFF(PreviousDate, CurrentDate, DAY) = 1, DATEDIFF(CurrentDate, _date, DAY), 0)

Here is the result.

Regards,

Nono Chen

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