Forum Discussion
Solved
Delta Calculation per Time Period for Group Values
I've seen a hundred posts on this but no real solution.
My data:
| Group | Date | Value |
| a | 1/13/2015 | 897 |
| a | 1/9/2015 | 200 |
| a | 1/1/2015 | 647 |
| b | 1/13/2015 | 2 |
| b | 1/9/2015 | 978 |
| b | 1/1/2015 | 655 |
| c | 1/13/2015 | 61 |
| c | 1/9/2015 | 36 |
| c | 1/1/2015 | 959 |
What I need: a delta calculation that subtracts the difference of each value from last week, for each group. Ergo:
| Group | Date | Value | Delta |
| a | 1/13/2015 | 897 | 697 |
| a | 1/9/2015 | 200 | -447 |
| a | 1/1/2015 | 647 | 0 |
| b | 1/13/2015 | 2 | -976 |
| b | 1/9/2015 | 978 | 323 |
| b | 1/1/2015 | 655 | 0 |
| c | 1/13/2015 | 61 | 25 |
| c | 1/9/2015 | 36 | -923 |
| c | 1/1/2015 | 959 | 0 |
Note where the delta is 0.
This is easily achieved in R:
dataset %>% group_by(Group) %>% mutate(Delta = Value - lag(Value, order_by=Date))
Unfortunately there's no real enterprise support for R in PBI Service. So how do we do this with M/DAX?
