cancel
Showing results for
Did you mean:
Member

## Need help with calculating row variance based on date selection in slicer

I'm drawing a blank on how to achieve what I feel like should be simple. Any help is appreciated!!!

Data Set looks like this:

 Customer Sales Date (English) Alpha 100 1/1/2019 Alpha 200 2/1/2019 Alpha 300 3/1/2019 Alpha 400 4/1/2019 Alpha 500 5/1/2019 Alpha 600 6/1/2019

What I am trying to achieve:

1) Matrix visual with Company as Row, Date as Column, and Sales as Value

2) Slicer based on Date field

3) I see the variance in sales between any two months selected in slicer (i.e. Jan and Mar)

Desired output looks like this after I select Jan and Mar from slicer:

 Customer Jan 2019 Mar 2019 Variance Alpha 100 300 -200

So in summary, I need to dynamically calculate the variance between two periods selected in a slicer.

Any ideas?

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: Need help with calculating row variance based on date selection in slicer

Hi  @Brysonds ,

Variance =
var a = CALCULATE(MAX(Table1[Sales]),FILTER(ALL(Table1),[Date (English)]=MAX(Table1[Date (English)])))
var b = CALCULATE(MAX(Table1[Sales]),FILTER(ALL(Table1),[Date (English)]=MIN(Table1[Date (English)])))
Return
b-a

Result shown as below:

pbix attached:

https://wicren-my.sharepoint.com/:u:/g/personal/dinaye_wicren_onmicrosoft_com/Eb2Q_95HzQlJnYKDcTSoga...

Best regards,

Dina Ye

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
4 REPLIES 4
Established Member

## Re: Need help with calculating row variance based on date selection in slicer

Hi @Brysonds ,

I just want to know if user select more than 2 selections in date slicer,

or when user don't select any filters, how does the chart show?

Aiolos Zhao

Community Support Team

## Re: Need help with calculating row variance based on date selection in slicer

Hi  @Brysonds ,

Variance =
var a = CALCULATE(MAX(Table1[Sales]),FILTER(ALL(Table1),[Date (English)]=MAX(Table1[Date (English)])))
var b = CALCULATE(MAX(Table1[Sales]),FILTER(ALL(Table1),[Date (English)]=MIN(Table1[Date (English)])))
Return
b-a

Result shown as below:

pbix attached:

https://wicren-my.sharepoint.com/:u:/g/personal/dinaye_wicren_onmicrosoft_com/Eb2Q_95HzQlJnYKDcTSoga...

Best regards,

Dina Ye

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
Member

## Re: Need help with calculating row variance based on date selection in slicer

Hi @v-diye-msft  Dina,

Thanks so much for taking the time to put this together. This gets me really close!

I tried using the formula you provided, and I get the following result. The variance is not calculating correctly

I tried altering the formula to this and it fixed the amounts, but has the wrong sign:

Variance =
var P1 = CALCULATE(MAX(CSE[Asset Amount]),FILTERS(CSE[Fiscal Year / Period]))
var P2 = CALCULATE(MIN(CSE[Asset Amount]),FILTERS(CSE[Fiscal Year / Period]))
Return
P2-P1

Here you can see that the variance for the first line is -27.20, but it should be positive (Jan - Feb asset amount).

Any ideas?

Also, is there a way to hide the "variance" columns that show 0 and only show the variance sub-total?

Community Support Team

## Re: Need help with calculating row variance based on date selection in slicer

Hi @Brysonds ,

Take a try of this:

Variance =
var P1 = CALCULATE(MAX(CSE[Asset Amount]),FILTERS(CSE[Fiscal Year / Period]))
var P2 = CALCULATE(MIN(CSE[Asset Amount]),FILTERS(CSE[Fiscal Year / Period]))
var P3 = CALCULATE(CSE[Fiscal Year / Period]),FILTER(CSE,CSE[Asset Amount]=P1))
var P4 = CALCULATE(CSE[Fiscal Year / Period]),FILTER(CSE,CSE[Asset Amount]=P2))
Return
IF(P3<P4,P1-P2,P2-P1)

Regarding to the 0 in the matrix, we can't remove it currently. probably you can adjust the font color or other format to make it unconspicuous.

Best regards,

Dina Ye

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.

Announcements

#### Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

#### Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

#### Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)