Calculate number of days between dates in same column in power query

[Anonymous]

Dummy data    First picture is the dummy data on which i need to peform the calculation. Second picture is the end result. I need to calculate the difference between dates based on index column.

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

Create measures

Measure = CALCULATE(SUM('Table'[Date]),FILTER(ALLEXCEPT('Table','Table'[Object]),[Index]=MAX([Index])-1))

Measure 2 = IF([Measure]<>BLANK(),DATEDIFF([Measure],MAX('Table'[Date]),DAY))

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[fhfortech]

I need an index column just like this, how can I created in power query

[Stachu]

you could add the previous date and previous object as new columns, as desribed here
https://www.thebiccountant.com/2018/07/12/fast-and-easy-way-to-reference-previous-or-next-rows-in-po...

then you can do simple if

if [Object]=[Previous Object] then [Date]-[Previous Date] else null

although you may need to add Duration.Days around the substracted dates, I wrote it from memory so not 100% sure it's needed