Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Dynamic Difference Measure Using Slicer

Hi All,

 

I have the following data structure (about 15 other data columns not shown here):

CoCodePeriodPTBI
Ent12016 YE100000
Ent12017 YE117558
Ent12018 Q1125469
Ent22016 YE449514
Ent22017 YE283007
Ent22018 Q1466238


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:

CoCode2016 YE2017 YEDifference
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?

v-chuncz-msft
Community Support
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

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

CountryFiling GroupCoCodeCY PTBIPY PTBIDifference
UKUK Group 1UK1                       1                 10
UKUK Group 1UK2                       1                 10
UKUK Group 2UK3        (300,000)  (300,000)0
UKUK Group 2UK4000
 Total         (299,998)                 2        (300,000)

 

Expected Matrix:

CountryFiling GroupCoCodeCY PTBIPY PTBIDifference
UKUK Group 1UK1                       1                 10
UKUK Group 1UK2                       1                 10
UKUK Group 2UK3        (300,000)0        (300,000)
UKUK Group 2UK4000
 Total         (299,998)                 2        (300,000)

 

Original Query:

CountryFiling GroupCoCodePeriodPTBI
UKUK Group 1UK112/31/20161
UKUK Group 1UK212/31/20161
UKUK Group 2UK412/31/20160
UKUK Group 1UK112/31/20171
UKUK Group 1UK212/31/20171
UKUK Group 2UK312/31/2017-300000
UKUK Group 2UK412/31/20170



parry2k
Super User
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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.