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