cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## calculating difference between current to previous most recent result per group

I have a table that looks similar to

 control result date 1 12 20-9-2019 1 15 22-9-2019 1 9 23-9-2019 2 10 20-9-2019 2 5 22-9-2019 2 23 23-9-2019 3 8 20-9-2019 3 34 22-9-2019 3 30 23-9-2019

I want to create a calculated new column that shows the difference between the result on that row and the result on the closest previous date per control (results come in with irregular intervals)

I want it to end up looking like

 control result date difference 1 12 20-9-2019 1 15 22-9-2019 3 1 9 23-9-2019 -6 2 10 20-9-2019 2 5 22-9-2019 -5 2 23 23-9-2019 18 3 8 20-9-2019 3 34 22-9-2019 26 3 30 23-9-2019 -4

How do i do this?

1 ACCEPTED SOLUTION
Community Support

Hi @Anonymous ,

Refering to the post @amitchandak provide, I create these columns:

```Index =
CALCULATE (
COUNTROWS ( 'Table' ),
FILTER (
ALLEXCEPT ( 'Table', 'Table'[control] ),
'Table'[date] < EARLIER ( 'Table'[date] )
)
) + 1```
```Value Change =
'Table'[result]
- CALCULATE (
SUM ( 'Table'[result] ),
FILTER (
ALLEXCEPT ( 'Table', 'Table'[control] ),
'Table'[Index]
= EARLIER ( 'Table'[Index] ) - 1
)
)```

Best Regards,

Icey

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

2 REPLIES 2
Community Support

Hi @Anonymous ,

Refering to the post @amitchandak provide, I create these columns:

```Index =
CALCULATE (
COUNTROWS ( 'Table' ),
FILTER (
ALLEXCEPT ( 'Table', 'Table'[control] ),
'Table'[date] < EARLIER ( 'Table'[date] )
)
) + 1```
```Value Change =
'Table'[result]
- CALCULATE (
SUM ( 'Table'[result] ),
FILTER (
ALLEXCEPT ( 'Table', 'Table'[control] ),
'Table'[Index]
= EARLIER ( 'Table'[Index] ) - 1
)
)```

Best Regards,

Icey

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.