Forum Discussion
Dynamic Difference Measure Using Slicer
Hi All, I have the following data structure (about 15 other data columns not shown here): CoCode Period PTBI Ent1 2016 YE 100000 Ent1 2017 YE 117558 Ent1 2018 Q1 125469 En...
v-chuncz-msft
Community Support
Community SupportAnonymous,
You may add the following measure.
Measure =
VAR p1 =
MIN ( 'Dataset'[Period] )
VAR p2 =
MAX ( 'Dataset'[Period] )
RETURN
IF (
ISINSCOPE ( 'Dataset'[Period] ),
SUM ( 'Dataset'[PTBI] ),
CALCULATE ( SUM ( 'Dataset'[PTBI] ), 'Dataset'[Period] = p2 )
- CALCULATE ( SUM ( 'Dataset'[PTBI] ), 'Dataset'[Period] = p1 )
)
Sam,
It turns out that that doesn't work quite as well as I originally thought... If a value isn't present for one of the periods, it says there's no difference, but still (correctly) includes the difference in the total, which seems odd. Also of note: I converted the periods to dates, so they read 12/31/2016, 03/31/2017, etc..
I've pasted a table of what it's doing, and what I'd expect it to do below. Any help you can provide would be greatly appreciated. Thanks!
Current Power BI Matrix:
| Country | Filing Group | CoCode | CY PTBI | PY PTBI | Difference |
| UK | UK Group 1 | UK1 | 1 | 1 | 0 |
| UK | UK Group 1 | UK2 | 1 | 1 | 0 |
| UK | UK Group 2 | UK3 | (300,000) | (300,000) | 0 |
| UK | UK Group 2 | UK4 | 0 | 0 | 0 |
| Total | (299,998) | 2 | (300,000) |
Expected Matrix:
| Country | Filing Group | CoCode | CY PTBI | PY PTBI | Difference |
| UK | UK Group 1 | UK1 | 1 | 1 | 0 |
| UK | UK Group 1 | UK2 | 1 | 1 | 0 |
| UK | UK Group 2 | UK3 | (300,000) | 0 | (300,000) |
| UK | UK Group 2 | UK4 | 0 | 0 | 0 |
| Total | (299,998) | 2 | (300,000) |
Original Query:
| Country | Filing Group | CoCode | Period | PTBI |
| UK | UK Group 1 | UK1 | 12/31/2016 | 1 |
| UK | UK Group 1 | UK2 | 12/31/2016 | 1 |
| UK | UK Group 2 | UK4 | 12/31/2016 | 0 |
| UK | UK Group 1 | UK1 | 12/31/2017 | 1 |
| UK | UK Group 1 | UK2 | 12/31/2017 | 1 |
| UK | UK Group 2 | UK3 | 12/31/2017 | -300000 |
| UK | UK Group 2 | UK4 | 12/31/2017 | 0 |