Compare 2 weeks in data table using 2 slicers

[Anonymous]

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 between them. For example:

Week Values
Slicer 1 ('STAFF DATA'[PERIODSW]) = 2136

Slicer 2 ('STAFF DATA'[PERIODSW]) = 2113

A measure then returns the count of Staff ID

Headcount = COUNTROWS(VALUES('STAFF DATA'[ID Number]))

Idealy this would enable management to see that we had 15 sales staff in week 2136, 14 sales staff in week 2113 with a net increase of 1 staff member.

I can only get this to work by manually specifying the week index value (142 in the below) in the measure formula for the comparison week, but need it to be dynamic based on the user's slicer input.

Headcount Prior Week = CALCULATE(COUNTROWS(VALUES('STAFF DATA'[ID Number])),ALL('STAFF DATA'[PERIODSW]),'STAFF DATA'[PERIODSW INDEX]=142)

Is there a way I can dynamically specify the index number based on the second slicer?

I know I could duplicate the data set, and prevent relationships to do this, however was hoping to avoid duplicating data where possible.

Thank you in advance!

[amitchandak]

@Anonymous , refer to my WOW blog on how week rank can help you to solve problems.

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8

[Anonymous]

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 · Accepted solution]

@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]

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