Dynamic Difference Measure Using Slicer

[Anonymous]

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
Ent2	2016 YE	449514
Ent2	2017 YE	283007
Ent2	2018 Q1	466238

What 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.

For example, the matrix would look like this:

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

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.

Difference =

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")

)

)

Thanks!

[Anonymous]

Can anyone provide any insight?

[v-chuncz-msft]

@Anonymous,

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 )
    )

[Anonymous]

@v-chuncz-msft 

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

[parry2k]

@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? 

or you want use to select two period and then give the difference between those two period?

[Anonymous]

@parry2k 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.