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

[Brysonds]

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?

[v-diye-msft · Accepted solution]

Hi  @Brysonds ,

I created the sample as your requested. then add the measure:

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

[Brysonds]

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?

[v-diye-msft]

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

[Anonymous]

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