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

[Anonymous]

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?  

[Anonymous]

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?

[Anonymous]

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?

[Iadem]

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

[v-ljerr-msft]

Hi @Anonymous,

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. 

Regards

[Anonymous]

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)

Report

Showing Connection with May Filter

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

[Anonymous]

Showing connection to May FilterShowing connection to June filterReport - 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? 

[v-ljerr-msft · Accepted solution]

Hi @Anonymous,

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. 

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