cancel
Showing results for
Did you mean:
lizsufrinko 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 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. ```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

7 REPLIES 7
lizsufrinko 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

## 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? 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. Regards

Iadem 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

## 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)``` 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.

lizsufrinko Member

## Dynamic percentage change and # change using a formula that uses the date filters as a reference Showing connection to May Filter Showing connection to June filter Report - 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
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. ```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

Announcements   