Dynamic Difference Measure Using Slicer

Question

Hi All,&nbsp;I have the following data structure (about 15 other data columns not shown here):CoCodePeriodPTBIEnt12016 YE100000Ent12017 YE117558Ent12018 Q1125469Ent22016 YE449514Ent22017 YE283007Ent22018 Q1466238What we want to do is take the dynamic difference in PTBI for the periods selected by the slicer, see example below. Any thoughts on how to accomplish this? Our goal would be to keep it pretty dynamic so that as we add periods to the dataset, we can change the comparisons.&nbsp;For example, the matrix would look like this:CoCode2016 YE2017 YEDifferenceEnt1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 100,000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 117,558&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 17,558Ent2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 449,514&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 283,007&nbsp;&nbsp;&nbsp; (166,507)&nbsp;&nbsp;I'm currently using the following DAX expression, but it's too static because it forces you to put in the columns that we want to compare, and it doesn't work well in a matrix because it flips the values on the earlier period's column.&nbsp;Difference&nbsp;= IF(HASONEVALUE('Dataset'[Total Total Pre-Tax Book Income]),BLANK(),CALCULATE(SUM('Dataset'[Total Total Pre-Tax Book Income]),FILTER('Dataset','Dataset'[Period] = "YE 2017"))- CALCULATE(SUM('Dataset'[Total Total Pre-Tax Book Income]),FILTER('Dataset','Dataset'[Period] = "YE 2016")))&nbsp;Thanks!

v-chuncz-msft · Answer

Anonymous,
&nbsp;
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 )
    )

parry2k · Answer

Anonymous do you want that user will select one period in slicer and then measure will look at the previous period and give the difference?&nbsp;
&nbsp;
or you want use to select two period and then give the difference between those two period?
&nbsp;
&nbsp;

anonymous · Answer

parry2k&nbsp;I'd like the user to select the two periods in one slicer, or if it's easier, two slicers to compare the two periods.

anonymous · Answer

v-chuncz-msft&nbsp;&nbsp;Sam,&nbsp;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.&nbsp; Also of note: I converted the periods to dates, so they read 12/31/2016, 03/31/2017, etc..&nbsp;I've pasted a table of what it's doing, and what I'd expect it to do below.&nbsp;Any help you can provide would be greatly appreciated. Thanks!&nbsp;Current Power BI Matrix:CountryFiling GroupCoCodeCY PTBIPY PTBIDifferenceUKUK Group 1UK1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10UKUK Group 1UK2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10UKUK Group 2UK3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (300,000)&nbsp; (300,000)0UKUK Group 2UK4000&nbsp;Total&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (299,998)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (300,000)&nbsp;Expected Matrix:CountryFiling GroupCoCodeCY PTBIPY PTBIDifferenceUKUK Group 1UK1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10UKUK Group 1UK2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10UKUK Group 2UK3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (300,000)0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (300,000)UKUK Group 2UK4000&nbsp;Total&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (299,998)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (300,000)&nbsp;Original Query:CountryFiling GroupCoCodePeriodPTBIUKUK Group 1UK112/31/20161UKUK Group 1UK212/31/20161UKUK Group 2UK412/31/20160UKUK Group 1UK112/31/20171UKUK Group 1UK212/31/20171UKUK Group 2UK312/31/2017-300000UKUK Group 2UK412/31/20170

anonymous · Answer

Can anyone provide any insight?

Forum Discussion

Dynamic Difference Measure Using Slicer

5 Replies

Related content

Dynamic Calculation Different Period

Dynamic measure filtering

Dynamic Measure developement

Cumulative Difference from Another measure

Dynamically rank calculated measure based on different groupings

CoCode	Period	PTBI
Ent1	2016 YE	100000
Ent1	2017 YE	117558
Ent1	2018 Q1	125469
Ent2	2016 YE	449514
Ent2	2017 YE	283007
Ent2	2018 Q1	466238

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)

CoCode	2016 YE	2017 YE	Difference
Ent1	100,000	117,558	17,558
Ent2	449,514	283,007	(166,507)