cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## 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 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!

5 REPLIES 5
Anonymous
Not applicable

Can anyone provide any insight?

Community Support

@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 )
)
```
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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

Super User

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

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

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