Measures calculated based on 2 values depending on 2 indepent slicers

[Anonymous]

Happy New year

So my subject sounds confusing - please bear with me explaining this.

Lets say my scenario is that I would like to compare number of prints over a period of time.

However, I need to give users flexiblity to adjust time lines for example

Slicer 1: between year 2011-2013

Slicer 2: between year 2015-2016

I do this by putting 2 separate line charts, first one interaction depends on the 1st slicer and 2nd line chart depends on the 2nd slicer etc...

Say the charts represent the number of photo copies made during that period.

Now I need to do a comparison between the difference betweens the average of print daily in the selected time lines.

How can I achieve this? See, my end users may want to compare data on weekly basis or monthly basis.

Hope you can advice

Thank you

[v-shex-msft · Accepted solution]

Hi @Anonymous,

guavaq's soution seems well, you can also take a look at below formulas which used to calculate the average value.

Weekly AVG Of Selected Range = 
var datemax= LASTDATE(ALLSELECTED(Table[Date]))
var datemin= FIRSTDATE(ALLSELECTED(Table[Date]))
return
SUMX(FILTER(ALL(Table),[Date]>=datemin&&[Date]<=datemax),[Amount])/DATEDIFF(datemin,datemax,WEEK)

Monthly AVG Of Selected Range = 
var datemax= LASTDATE(ALLSELECTED(Table[Date]))
var datemin= FIRSTDATE(ALLSELECTED(Table[Date]))
return
SUMX(FILTER(ALL(Table),[Date]>=datemin&&[Date]<=datemax),[Amount])/DATEDIFF(datemin,datemax,MONTH)

AVG of Selected Range = 
var datemax= LASTDATE(ALLSELECTED(Table[Date]))
var datemin= FIRSTDATE(ALLSELECTED(Table[Date]))
return
AVERAGEX(FILTER(ALL(Table),[Date]>=datemin&&[Date]<=datemax),[Amount])

Notice, table is the source data of slicer.

Regards,

Xiaoxin Sheng

[GilbertQ]

Hi @Anonymous,

What I would do to achieve this is to create a Calculated table which is a copy of your existing table. You can use the following by clicking on Modeling and then New Table

Then the syntax would be:

Table = TableToCopy

Then what you could do is to then create a calculated measure on your "Table" which would be the difference between "TableToCopy" and "Table"

Finally your one visual with it's slicer would be from "TableToCopy" and your other visual and slicer would be from "Table"

And then your calculated measure "Difference" would be showing the difference between what was selected?