cancel
Showing results for
Search instead for
Did you mean:
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

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

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

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

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

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.

Member

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

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?

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

## Helpful resources

Announcements

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

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

#### Power Platform World Tour

Find out where you can attend!

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