Forum Discussion
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 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.
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!
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 ))
4 Replies
- amitchandak
Super User
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=pnAesWxYgJ8Hi 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
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 ))