Forum Discussion
Measure calculating variance between ID
Hi,
Here is my table.
IDProcessing CentreTeamCategorySub-CategoryDateNumber Oustanding ItemsDollar Value
| 2023402 | x | yy | a | 2023-04-15 | 80 | $ 1,100.00 | |
| 2023402 | x | yy | a | 2023-04-15 | 80 | $ 31,100.00 | |
| 2023402 | x | yy | a | 2023-04-15 | 1 | $ 800.00 | |
| 2023402 | x | yy | a | 2023-04-15 | |||
| 2023402 | x | yy | a | 2023-04-15 | 16 | $ 4,400.00 | |
| 2023402 | x | yy | a | 2023-04-15 | 5 | ||
| 2023402 | x | yy | b | High | 2023-04-15 | 3727 | |
| 2023402 | x | yy | b | Medium | 2023-04-15 | 416 | |
| 2023402 | x | yy | b | Low | 2023-04-15 | 382 | |
| 2023402 | x | yy | b | Nil | 2023-04-15 | 921 | |
| 2023402 | x | yy | b | Non-Payment | 2023-04-15 | 217 | $ 9,181,600.00 |
| 2023402 | x | yy | b | Converted | 2023-04-15 | 280 | |
| 2023402 | x | yy | b | Refunds | 2023-04-15 | ||
| 2023501 | x | yy | a | 2023-05-01 | 1 | $ 4,443.29 | |
| 2023501 | x | yy | a | 2023-05-01 | 21 | $ 5,683.29 | |
| 2023501 | x | yy | a | 2023-05-01 | 2 | $ 800.00 | |
| 2023501 | x | yy | a | 2023-05-01 | 7 | $ 800.00 | |
| 2023501 | x | yy | a | 2023-05-01 | 15 | $ 6,800.00 | |
| 2023501 | x | yy | a | 2023-05-01 | 5 | ||
| 2023501 | x | yy | b | High | 2023-05-01 | 1550 | |
| 2023501 | x | yy | b | Medium | 2023-05-01 | 500 | |
| 2023501 | x | yy | b | Low | 2023-05-01 | 120 | |
| 2023501 | x | yy | b | Nil | 2023-05-01 | 1230 | |
| 2023501 | x | yy | b | Non-Payment | 2023-05-01 | 100 | $ 915,200.00 |
| 2023501 | x | yy | b | Converted | 2023-05-01 | 270 | |
| 2023501 | x | yy | b | Refunds | 2023-05-01 | ||
| 2023502 | x | yy | a | 2023-05-15 | 500 | $ 43,400.00 | |
| 2023502 | x | yy | a | 2023-05-15 | 100 | $ 43,400.00 | |
| 2023502 | x | yy | a | 2023-05-15 | 4 | $ 2,600.00 | |
| 2023502 | x | yy | a | 2023-05-15 | 1 | $ 800.00 | |
| 2023502 | x | yy | a | 2023-05-15 | 15 | $ 7,500.00 | |
| 2023502 | x | yy | a | 2023-05-15 | 7 | ||
| 2023502 | x | yy | b | High | 2023-05-15 | 3000 | |
| 2023502 | x | yy | b | Medium | 2023-05-15 | 256 | |
| 2023502 | x | yy | b | Low | 2023-05-15 | 800 | |
| 2023502 | x | yy | b | Nil | 2023-05-15 | 1200 | |
| 2023502 | x | yy | b | Non-Payment | 2023-05-15 | 229 | $ 1,956,300.00 |
| 2023502 | x | yy | b | Converted | 2023-05-15 | 249 | |
| 2023502 | x | yy | b | Refunds | 2023-05-15 |
I am looking for help to build a measure that would calculate the variance of outstanding items between the latest period (either use date column or the ID column) and the period just before the latest. In this example, it would be the variance between period with ID 2023502 and 2023501.
Thank you.
Whoule , try a measure like
Variance Items =
VAR _lastest = MAX('Table'[ID])
VAR _previous= CALCULATE(MAX('Table'[ID]), 'Table'[ID] < _lastest)VAR _latest_out = CALCULATE(SUM('Table'[Number Oustanding Items]), 'Table'[ID] = _lastest)
VAR _previous_out = CALCULATE(SUM('Table'[Number Oustanding Items]), 'Table'[ID] = PreviousPeriodID)RETURN
IF(ISBLANK(_latest_out) || ISBLANK(_previous_out), BLANK(),
_latest_out - _previous_out)
1 Reply
Whoule , try a measure like
Variance Items =
VAR _lastest = MAX('Table'[ID])
VAR _previous= CALCULATE(MAX('Table'[ID]), 'Table'[ID] < _lastest)VAR _latest_out = CALCULATE(SUM('Table'[Number Oustanding Items]), 'Table'[ID] = _lastest)
VAR _previous_out = CALCULATE(SUM('Table'[Number Oustanding Items]), 'Table'[ID] = PreviousPeriodID)RETURN
IF(ISBLANK(_latest_out) || ISBLANK(_previous_out), BLANK(),
_latest_out - _previous_out)