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 ))

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi amitchandak,

 

I've actually used your "week is not so weak" post to help me address a number of issues with our 52 week reporting requirements and it has been immensely helpful.

 

This problem I think is slightly different in that the end user needs the ability to select a comparison week with a slicer. It could be any week from any year with no predictable pattern.

 

If I have a slicer from an unrelated table, that has the same weeks that correlate to the same week index number, how do I replicate that in my comparison measure?

Manual Formula Works

Headcount Week LY (Test) = CALCULATE(COUNTROWS(VALUES('STAFF DATA'[ID Number])),FILTER(ALL('STAFF DATA'[PERIODSW]),'STAFF DATA'[PERIODSW]="2113"))

 

Formula stops working when attempting to link to unrelated slicer (Replaced "2113" with SELECTEDVALUE(PERIODSW[PERIODW])

 

Headcount Week LY (Test) = CALCULATE(COUNTROWS(VALUES('STAFF DATA'[ID Number])),FILTER(ALL('STAFF DATA'[PERIODSW]),'STAFF DATA'[PERIODSW]=SELECTEDVALUE(PERIODSW[PERIODW])))

 

 

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))