Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Compare 2 weeks in data table using 2 slicers

Hi all,   I am creating a HR report and one of the items I have been asked to implement is to have 2 slicers where management can compare the count of each job position, and calculate the variance ...
  • amitchandak's avatar
    amitchandak
    5 years ago

    Anonymous , Assume you have an independent date table and you want to use a period as a filter on Fact from week table, which is sortable (subtract -1)

    //Period or any column from week table is selected on slicer

     

    Try like this

     

    This Week =
    var _max1 = maxx(allselected('Date'), 'Date'[Week Rank])
    var _max = maxx(filter(all('Date'),'Date'[Week Rank] =_max), 'Date'[Period]) //Period we want to use in fact
    return
    CALCULATE(sum('order'[Qty]), FILTER('order', 'order'[Period]=_Max ))


    Last Week =
    var _max1 = maxx(allselected('Date'), 'Date'[Week Rank])-1
    var _max = maxx(filter(all('Date'),'Date'[Week Rank] =_max), 'Date'[Period]) //Period we want to use in fact
    return
    CALCULATE(sum('order'[Qty]), FILTER('order', 'order'[Period]=_Max ))

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

Anonymous , Assume you have an independent date table and you want to use a period as a filter on Fact from week table, which is sortable (subtract -1)

//Period or any column from week table is selected on slicer

 

Try like this

 

This Week =
var _max1 = maxx(allselected('Date'), 'Date'[Week Rank])
var _max = maxx(filter(all('Date'),'Date'[Week Rank] =_max), 'Date'[Period]) //Period we want to use in fact
return
CALCULATE(sum('order'[Qty]), FILTER('order', 'order'[Period]=_Max ))


Last Week =
var _max1 = maxx(allselected('Date'), 'Date'[Week Rank])-1
var _max = maxx(filter(all('Date'),'Date'[Week Rank] =_max), 'Date'[Period]) //Period we want to use in fact
return
CALCULATE(sum('order'[Qty]), FILTER('order', 'order'[Period]=_Max ))

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Fantastic thanks for your help amitchandak 

I was able to modify the formula to get it working with my table. I had to change the ALL (in the calculate line) to reference the column as opposed to the whole table. Otherwise all individual job roles reported the total instead of the count of that role.

 

Comparison Week =
var _max1 = MAXX(ALLSELECTED('PERIODSW'), PERIODSW[Week Index])
var _max = MAXX(FILTER(ALL(PERIODSW), PERIODSW[Week Index] =_max1), PERIODSW[PERIODW]) //Period we want to use in fact
RETURN
CALCULATE([Headcount],FILTER(ALL('STAFF DATA'[PERIODSW]),'STAFF DATA'[PERIODSW]=_Max))