calcultate previous week according to the row selection dynamically in matrix

[Anonymous]

hi all,

what i want to achieve is that in Matrix, it could calculate previous week amount smartly according to the field that i put in row section: e.g. 

when i put country in Rows, it shows

and if i put product in Rows, it will dynamtically change to

I am able to get previous week amount by the following DAX, however the problem is, because i sepecify Country in the DAX, if i want to calculate product, I would need to create another measure for it. What I want is just 1 measure to achieve this dynamic change, is it possible? 

PreviousWeekPaidImpr =

var currentweeknum = SELECTEDVALUE(bq[weeknum cloumn])

return

CALCULATE([Paid Impr],FILTER(ALLEXCEPT(bq,bq[country]),bq[weeknum cloumn]= currentweeknum -1))

FYI, I tried 

PreviousWeekPaidImpr =

var currentweeknum = SELECTEDVALUE(bq[weeknum cloumn])

return

CALCULATE([Paid Impr],FILTER(ALL(bq),bq[weeknum cloumn]= currentweeknum -1))

but with this, it returns result: which is not what I want. 

[Greg_Deckler]

@Anonymous Maybe:

PreviousWeekPaidImpr =
var currentweeknum = SELECTEDVALUE(bq[weeknum cloumn])
var country = CALCULATE([Paid Impr],FILTER(ALLEXCEPT(bq,bq[country]),bq[weeknum cloumn]= currentweeknum -1))
var product = CALCULATE([Paid Impr],FILTER(ALLEXCEPT(bq,bq[product]),bq[weeknum cloumn]= currentweeknum -1))
RETURN
  IF(ISINSCOPE('bq'[country]),country,product)

[Anonymous]

hi @Greg_Deckler thanks for the quick reply.

there are acutally more than 2 fields that i want to change in the Rows section. Apart from country and products which are already mentioned, there are also sizes, colours and clients etc. 
In this case, the IF function would not be able to cover all the scenario. 

Do you have a new solution for it? 

[Greg_Deckler]

@Anonymous You could do a nested IF statement but a SWITCH TRUE would be better, like below. Also, are you using a Field Parameter or no?

PreviousWeekPaidImpr =
var currentweeknum = SELECTEDVALUE(bq[weeknum cloumn])
var country = CALCULATE([Paid Impr],FILTER(ALLEXCEPT(bq,bq[country]),bq[weeknum cloumn]= currentweeknum -1))
var product = CALCULATE([Paid Impr],FILTER(ALLEXCEPT(bq,bq[product]),bq[weeknum cloumn]= currentweeknum -1))
RETURN
  SWITCH(TRUE(),
    ISINSCOPE('bq'[country]),country,
    ISINSCOPE('bq'[product]),product,
    // more ISINSCOPE here
  )

[Anonymous]

hi @Greg_Deckler  thanks for the reply! I tired the swtich to calcaulate the difference between this week and previous week. However, the issue with swtich is that the sum total of the difference is missing if i use swtich. Do you have a way to work around this? I need the total number for futhere calculation steps. 

[Anonymous]

hi @Greg_Deckler  in case you missed this, I'm tagging here again.  it's highly appreciated if you have a solution for this, thanks!