Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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.
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.
I need an index column just like this, how can I created in power query
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