cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Ultima7e
New Member

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.

 

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!

1 ACCEPTED SOLUTION

@Ultima7e , 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 ))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

4 REPLIES 4
amitchandak
Super User IV
Super User IV

@Ultima7e , 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-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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

 

 

@Ultima7e , 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 ))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors