Calculating the number of weeks based on slicer context but ignoring the visualization context

[Anonymous]

Hi, everyone. I am working on a report in Power BI and I am banging my head against the wall on a particular problem! I am hoping the hive mind can save my sanity.

I have two queries. One holds all the dates when people worked. It essentially represents the universe of possible work dates. The other query is a summary table of users, the hours they worked, and any PTO hours they worked. These queries are connected by the Username (and not by date).

I am trying to calculate the Work efficiency of users. So I need to find the start and end dates of the range they were expected to work (from Table B below, and in Power BI, this is a slicer) and then I need to count the number of weeks this represents. This number should be the same for all users. In other words, I want to know the work efficiency for all users over the last two weeks.

I then take the number of weeks, multiply it by 40. I then back out PTO and then use it to divide into the total hours worked. In excel, it is obviously pretty easy, as follows.

Table A				Table B
User Name	Hours Worked	PTO		Possible Work Dates	Resource	Hours Worked
Scott	60	20		6-Oct-17	Scott	8
Lucy	60	0		9-Oct-17	Scott	9
Gil	60	10		12-Oct-17	Lucy	8
				etc...
User Name	Hours Worked	PTO	**Efficiency**
Scott	60	20	100%
Lucy	60	0	75%
Gil	60	10	86%
Calculation in Excel
2	=WEEKNUM(MAX(E5:E7))-WEEKNUM(MIN(E5:E7))+1

However, using DAX in Power BI, I can't figure out how to get the same number of weeks that should have been worked. My two queries are related, but by Username and not by date. And if I do calculate the difference in weeks (max - min), I either get the individual values for each user (e.g. Scott would show 2 weeks, Lucy would show one week, etc.) by using datedif, or I can use maxx and minx to get the full range of differences in the dates, totally ignoring the filter context being passed in from a slicer. 

What I actually need is to only use the filter context while ignoring the visualization context.

I hope this description makes sense. Like I said, I have been banging my head on this one. I am hoping there is a simple answer!

Best,

Scott

[BILASolution]

Hi @Anonymous

To get your answer try these steps...

1. I created a new table called "Date" with th next DAX expression...

Date = CALENDAR("01/01/2017";"31/12/2017") --The range depends of your case

2. I created a new table called "Users" with the next DAX expression...

Users = DISTINCT(Efficiency[Resource]) 

NOTE: Effciency table is "Table B" in your example. Efficiency looks like this...

3. Then, I created the next relationship between tables.

4. I created the next measures into Efficiency table...

Total Hours Worked = SUM(Efficiency[Hours Worked]) 

Total Weeks = DISTINCTCOUNT('Date'[Week Number of Year]) 

Weeks to Hours = IF(ISBLANK([Total Hours Worked]);BLANK();CALCULATE([Total Weeks] * 40;ALL(Users[User]))) 

Efficiency = DIVIDE([Total Hours Worked];[Weeks to Hours])

Message = "Efficiency by User: " & FIRSTNONBLANK('Date'[Date];1) & " To " & LASTNONBLANK('Date'[Date];1) 

5. This is how the final report looks...(after hiding useless columns)

Regards

BILASolution

[Anonymous]

Thanks, @BILASolution! That feels like it is close, but my problem is that some people do not show up in a week at all. So I cannot just get their related values. I need to get the range of weeks that meet the slicer criteria. So if I have two users, one of whom worked one week and one of whom worked both weeks, I still need my calculation to know that both users should show two weeks. The distinctcount solution still looks for their related records. But since this is variable, I need to only have the slicer context in my efficiency calculation. I hope my description makes sense. And I really appreciate your help!

Scott

[BILASolution]

Hi @Anonymous

Try with this measure...

Total Weeks = 

var mindate = FIRSTNONBLANK('Date'[Date];1)
var maxdate = LASTNONBLANK('Date'[Date];1)
var minefdate = FIRSTNONBLANK(Efficiency[Possible work Dates];1)
var maxefdate = LASTNONBLANK(Efficiency[Possible work Dates];1)

 return SWITCH(TRUE();
      
           AND(MIN('Date'[Date]) >= MIN(Efficiency[Possible work Dates]);MAX('Date'[Date]) >= MAX(Efficiency[Possible work Dates])) ; 

           CALCULATE(DISTINCTCOUNT('Date'[Week Number of Year]);ALL('Date');'Date'[Date] >= mindate;'Date'[Date]<maxefdate) ;

           AND(MIN('Date'[Date]) >= MIN(Efficiency[Possible work Dates]);MAX('Date'[Date]) < MAX(Efficiency[Possible work Dates])) ; 

           CALCULATE(DISTINCTCOUNT('Date'[Week Number of Year]);ALL('Date');'Date'[Date] >= mindate;'Date'[Date]<maxdate) ;
               
           AND(MIN('Date'[Date]) < MIN(Efficiency[Possible work Dates]);MAX('Date'[Date]) >= MAX(Efficiency[Possible work Dates])) ; 

           CALCULATE(DISTINCTCOUNT('Date'[Week Number of Year]);ALL('Date');'Date'[Date] >= minefdate;'Date'[Date]<maxefdate) ;

           AND(MIN('Date'[Date]) < MIN(Efficiency[Possible work Dates]);MAX('Date'[Date]) < MAX(Efficiency[Possible work Dates])) ; 

           CALCULATE(DISTINCTCOUNT('Date'[Week Number of Year]);ALL('Date');'Date'[Date] >= minefdate;'Date'[Date]<maxdate)
)

This is the new final report...

I hope this answer your question...

Regards

BILASolution

[Anonymous · Accepted solution]

Thanks for all the work and thought, @BILASolution! I actually modified your first solution to make this work for me. What I had to do was take the count weeks Measure, and put it on the dimDate table instead of the Worked table. I then had to change my efficiency measure so that it no longer used the Calculate function, since that was forcing row context in my PowerBI visualization. Once I had the weeknum calculation in my dimDate column, I just used the following (painfully!) simple formula:

_Weeks = maxx(dimDate,dimDate[_WeekNum])-minx(dimDate,dimDate[_WeekNum]) +1

Your first solution is what got me 80% of the way there, so thanks!

Scott