Forum Discussion
calculate Difference from previous date
Good day,
Can anyone help me to get below output column in DAX.
I want to calculate difference between values columns based on Dates column
| name | DATES | VALUES | output |
| A | 08-12-2019 04:00 | 25 | |
| A | 09-12-2019 04:00 | 10 | 25 |
| A | 10-12-2019 04:00 | 15 | 10 |
| A | 11-12-2019 04:00 | 10 | 15 |
| A | 12-12-2019 04:00 | 5 | 10 |
| A | 13-12-2019 04:00 | 6 | 5 |
| A | 14-12-2019 04:00 | 54 | 6 |
| A | 15-12-2019 04:00 | 65 | 54 |
| B | 08-12-2019 04:00 | 22 | |
| B | 09-12-2019 04:00 | 47 | 22 |
| B | 10-12-2019 04:00 | 51 | 47 |
| B | 11-12-2019 04:00 | 28 | 51 |
| B | 12-12-2019 04:00 | 64 | 28 |
| B | 13-12-2019 04:00 | 85 | 64 |
| B | 14-12-2019 04:00 | 16 | 85 |
| C | 08-12-2019 04:00 | 91 | |
| C | 09-12-2019 04:00 | 22 | 91 |
| C | 10-12-2019 04:00 | 52 | 22 |
| C | 11-12-2019 04:00 | 82 | 52 |
| C | 12-12-2019 04:00 | 75 | 82 |
| C | 13-12-2019 04:00 | 64 | 75 |
| C | 14-12-2019 04:00 | 49 | 64 |
| C | 15-12-2019 04:00 | 18 | 49 |
| C | 16-12-2019 04:00 | 82 | 18 |
| D | 08-12-2019 04:00 | 27 | 82 |
| D | 09-12-2019 04:00 | 64 | |
| D | 10-12-2019 04:00 | 73 | 64 |
| D | 11-12-2019 04:00 | 35 | 73 |
| D | 12-12-2019 04:00 | 48 | 35 |
| D | 13-12-2019 04:00 | 64 | 48 |
| D | 14-12-2019 04:00 | 32 | 64 |
Thanks,
Yogesh
This expression works in a calculated column to get your desired result. Replace "Data" throughout with your actual table name.
Previous Value = VAR thisdatetime = Data[DATES] VAR prevdatetime = CALCULATE ( MAX ( Data[DATES] ), ALLEXCEPT ( Data, Data[name] ), Data[DATES] < thisdatetime ) RETURN CALCULATE ( MIN ( Data[VALUES] ), ALLEXCEPT ( Data, Data[name] ), Data[DATES] = prevdatetime )If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
1 Reply
- mahoneypat
Microsoft Employee
This expression works in a calculated column to get your desired result. Replace "Data" throughout with your actual table name.
Previous Value = VAR thisdatetime = Data[DATES] VAR prevdatetime = CALCULATE ( MAX ( Data[DATES] ), ALLEXCEPT ( Data, Data[name] ), Data[DATES] < thisdatetime ) RETURN CALCULATE ( MIN ( Data[VALUES] ), ALLEXCEPT ( Data, Data[name] ), Data[DATES] = prevdatetime )If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat