cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
lizsufrinko Member
Member

Using filters to show two measures at same time, trouble showing calculated difference

Okay new to PBI...

This is what I'm trying to accomplish. 

I have a page  and say I have a calculated measure card visual A and B. I also have two date filters on my page, each assigned to either A or B. This is to accomplish showing the difference between two months at the same time. 

 

Then I created a calculated measure (also a card visual) to take the difference (B-A) to show the amount of change from MoM on the same page. The issue here is that, it is not taking the correct difference. The sum is coming back as zero because B is the same formula as A. 

I do not have this card visual connected to either of the two date filters.

 

 

Using these same sort of techniques, what is the best way to get the difference (b-a) to show simply on this same page?  

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft v-ljerr-msft
Microsoft

Re: Dynamic percentage change and # change using a formula that uses the date filters as a reference

Hi @lizsufrinko,

 

Instead of using two date slicers, you can use just a single date slicer to pick up a date range(i.e. from 2017/05/01 to 2017/06/01). Then the formulas(untested) below should work dynamically in this scenario. Smiley Happy

sum of A % difference from June 2017 =
VAR minDate =
    MIN ( 'Table3'[Date] )
VAR maxDate =
    MAX ( 'Table3'[Date] )
VAR __BASELINE_VALUE =
    CALCULATE ( 'Table3'[sum of A], TREATAS ( { ( minDate ) }, 'Table3'[Date] ) )
VAR __MEASURE_VALUE =
    CALCULATE ( 'Table3'[sum of A], TREATAS ( { ( maxDate ) }, 'Table3'[Date] ) )
RETURN
    IF (
        NOT ISBLANK ( __MEASURE_VALUE ),
        DIVIDE ( __MEASURE_VALUE - __BASELINE_VALUE, __BASELINE_VALUE )
    )

 

Regards

View solution in original post

7 REPLIES 7
lizsufrinko Member
Member

Re: Using filters to show two measures at same time, trouble showing calculated difference

With research I just discovered power bi's quick measures and have tried using it. Still with no success though... How can I use quick measures for what I described above?

lizsufrinko Member
Member

Re: Using filters to show two measures at same time, trouble showing calculated difference

Okay So I got quick measures to calculate the MoM change... except now it does not change with A &B date filters! Any solution to making a quick measure correspond with filters?

Microsoft v-ljerr-msft
Microsoft

Re: Using filters to show two measures at same time, trouble showing calculated difference

Hi @lizsufrinko,

 

Could you post your table structures with some sample data, and your expected result? So that we can better assist on the formula for the measure. Smiley Happy

 

Regards

Iadem Frequent Visitor
Frequent Visitor

Re: Using filters to show two measures at same time, trouble showing calculated difference

If i understand you correctly, I think you need something like that:

Test = 
IF (
    HASONEVALUE ( 'Filter1'[Filter1] );
    SWITCH (
        VALUES ( 'Filter1'[Filter1] );
        "Filters1_value"; IF (
            HASONEVALUE ( 'Filter2'[Filter2] );
            SWITCH (
                VALUES ( 'Filter2'[Filter2]);
                "Filters2_value"; [Measure];
                "Filters2_value"; [Measure];
                BLANK ()
            );
            BLANK ()
        );
        "Filter1_value"; IF (
            HASONEVALUE ( 'Filter2'[Filter2]);
            SWITCH (
                VALUES ( 'Filter2'[Filter2]);
                "Filters2_value"; [Measure];
                "Filters2_value"; [Measure];
                BLANK ()
            );
            BLANK ()
        );
        BLANK ()
    )
)

 

 

lizsufrinko Member
Member

Re: Using filters to show two measures at same time, trouble showing calculated difference

sum of A % difference from June 2017 = 
VAR __BASELINE_VALUE =
	CALCULATE(
		'Table3'[sum of A],
		TREATAS({(DATE(2017, 5, 1))}, 'Table3'[Date])
	)
VAR __MEASURE_VALUE =
	CALCULATE(
		'Table3'[sum of A],
		TREATAS({(DATE(2017, 6, 1))}, 'Table3'[Date])
	)
RETURN
	IF(
		NOT ISBLANK(__MEASURE_VALUE),
		DIVIDE(__MEASURE_VALUE - __BASELINE_VALUE, __BASELINE_VALUE)
	)
sum of A difference from June 2017 = 
VAR __BASELINE_VALUE =
	CALCULATE(
		'Table3'[sum of A],
		TREATAS({(DATE(2017, 6, 1))}, 'Table3'[Date])
	)
VAR __MEASURE_VALUE =
	CALCULATE(
		'Table3'[sum of A],
		TREATAS({(DATE(2017, 5, 1))}, 'Table3'[Date])
	)
RETURN
	IF(NOT ISBLANK(__MEASURE_VALUE), __MEASURE_VALUE -  __BASELINE_VALUE)

1.PNGReport

 

 

2.PNGShowing Connection with May Filter

 

 

3.PNGShowing connection with June Filter

 

 So I have two cards called A. They are the same summation measure. They show different numbers because they are each connected to a different date filter that you can see in those photos. Then I want to calculate a # change and percentage change from these two different values. I posted the formulas above. The issue is that the date is hard coded in both those formulas, so if I were to change my date slider to see Jan vs. Feb., for example, the # change and percentage change display as "(blank)". 

 

How can I accomplish a dynamic percentage change and # change using a formula that uses the date filters as a reference or some other solution? 

 

Thank you v-ljerr-msft I will try utilizing this formula. 

lizsufrinko Member
Member

Dynamic percentage change and # change using a formula that uses the date filters as a reference

Screen Shot 2017-06-18 at 2.33.50 PM.pngShowing connection to May FilterScreen Shot 2017-06-18 at 2.33.55 PM.pngShowing connection to June filterScreen Shot 2017-06-18 at 2.34.02 PM.pngReport - if date is changed in either filter to show jan. or feb. the % diff and # diff. does not change

 

% Difference calc: 

sum of A % difference from June 2017 = 
VAR __BASELINE_VALUE =
	CALCULATE(
		'Table3'[sum of A],
		TREATAS({(DATE(2017, 5, 1))}, 'Table3'[Date])
	)
VAR __MEASURE_VALUE =
	CALCULATE(
		'Table3'[sum of A],
		TREATAS({(DATE(2017, 6, 1))}, 'Table3'[Date])
	)
RETURN
	IF(
		NOT ISBLANK(__MEASURE_VALUE),
		DIVIDE(__MEASURE_VALUE - __BASELINE_VALUE, __BASELINE_VALUE)

# difference calc: 

sum of A difference from June 2017 = 
VAR __BASELINE_VALUE =
	CALCULATE(
		'Table3'[sum of A],
		TREATAS({(DATE(2017, 6, 1))}, 'Table3'[Date])
	)
VAR __MEASURE_VALUE =
	CALCULATE(
		'Table3'[sum of A],
		TREATAS({(DATE(2017, 5, 1))}, 'Table3'[Date])
	)
RETURN
	IF(NOT ISBLANK(__MEASURE_VALUE), __MEASURE_VALUE -  __BASELINE_VALUE)

 So I have two cards called A. They are using the same summation measure. They show different numbers because they are each connected to a different date filter that you can see in those photos. Then I want to calculate a # change and percentage change from these two different values. I posted the formulas above. The issue is that the date is hard coded in both those formulas, so if I were to change my date slider to see Jan vs. Feb., for example, the # change and percentage change display as "(blank)". 

 

How can I accomplish a dynamic percentage change and # change using a formula that uses the date filters as a reference or some other solution? 

 

Microsoft v-ljerr-msft
Microsoft

Re: Dynamic percentage change and # change using a formula that uses the date filters as a reference

Hi @lizsufrinko,

 

Instead of using two date slicers, you can use just a single date slicer to pick up a date range(i.e. from 2017/05/01 to 2017/06/01). Then the formulas(untested) below should work dynamically in this scenario. Smiley Happy

sum of A % difference from June 2017 =
VAR minDate =
    MIN ( 'Table3'[Date] )
VAR maxDate =
    MAX ( 'Table3'[Date] )
VAR __BASELINE_VALUE =
    CALCULATE ( 'Table3'[sum of A], TREATAS ( { ( minDate ) }, 'Table3'[Date] ) )
VAR __MEASURE_VALUE =
    CALCULATE ( 'Table3'[sum of A], TREATAS ( { ( maxDate ) }, 'Table3'[Date] ) )
RETURN
    IF (
        NOT ISBLANK ( __MEASURE_VALUE ),
        DIVIDE ( __MEASURE_VALUE - __BASELINE_VALUE, __BASELINE_VALUE )
    )

 

Regards

View solution in original post

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (5,124)