CALCULATE DIFFERENCE (IN %) BETWEEN FIXED NUMBER AND THE LATEST NUMBER SELECTED

[Anonymous]

Hi!

This graph shows the number of calls and non-calls per region, and I need to create a measure to calculate the difference (in percentage) between the CW 11 – our reference - and the latest column selected by the user.

In this case, for example: CW 27 divided by CW11.

Obs: CW 27 can be any other week, and CW11 is fixed.

I can’t do that because I don’t know how to “ignore” on calculation the numbers between the columns and how to consider the latest CW selected.

*Caledar week: CW

Do you have any idea, please?

[amitchandak]

@Anonymous , in case you have date and date table

measure =

var _min = minx(allselected(Date),Date[Date])
var _max = maxx(allselected(Date),Date[Date])

return
calculate( [measure], filter(all(Date), Date[Date]=_max)) - calculate( [measure], filter(all(Date), Date[Date]=_min ))

if you have week and week table, else have week table

measure =

var _min = minx(allselected(week),week[week])
var _max = maxx(allselected(week),week[week])

return
calculate( [measure], filter(all(Date), Date[Date]=_max)) - calculate( [measure], filter(all(Date), Date[Date]=_min ))

[Anonymous]

Hi, Mr. @amitchandak.

Thanks for your help!

I have a table with weekly data, so I need to consider this one:

measure =

var _min = minx(allselected(week),week[week])
var _max = maxx(allselected(week),week[week])

return
calculate( [measure], filter(all(Date), Date[Date]=_max)) - calculate( [measure], filter(all(Date), Date[Date]=_min ))

But I still have some doubts...

I'm trying to replace by my columns (name of table is "Traffic Development"), but it's not working. What is wrong?

I put in red above the part of measure that i didn't understand.

var _min = MINX(ALLSELECTED('Traffic Development','Traffic Development'[Calendar Week])

var _max = MAXX(ALLSELECTED('Traffic Development','Traffic Development'[Calendar Week])

Thanks again!

[v-jayw-msft]

Hi @Anonymous ,

The logic is to calculate the values for earliest week and latest week in your visual.

I noticed that in your formula you lost ")" in ALLSELECTED() function.

Should be:
MINX(ALLSELECTED('Traffic Development'),'Traffic Development'[Calendar Week])

Best Regards,

Jay

[Anonymous · Accepted solution]

Hi, @v-jayw-msft ,

Thanks for your reply.

I tried to add it, but still didn't work.

Anyway I found another solution:

Minimum value = CALCULATE(MIN('Traffic Development'[Total of Contacts]), FILTER('Traffic Development', 'Traffic Development'[Index] = MIN('Traffic Development'[Index])))

Maximum value = CALCULATE(MAX('Traffic Development'[Total of Contacts]), FILTER('Traffic Development', 'Traffic Development'[Index] = MAX('Traffic Development'[Index])))

Difference between last and first value = [Maximum value]/[Minimum value]-1

An finally it worked out. 🙂