Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello, PowerBi newbie here:
I have the following two tables below and I am trying to make a measure that calculates:
Number of Business Days – Returns the count of IsBusinessDays in a filtered reporting period. This is determined with a date slicer from the Date Table
How do I get a count of the Number of Business Day that Member 2 should be calculated for when they hired in during the reporting period?
Member Table
Name | Hire Date |
Member1 | 12/31/2019 |
Member2 | 1/6/2020 |
Member3 | 1/1/2007 |
Date Table
Date | IsBusinessDay |
1/1/2020 | 0 |
1/2/2020 | 1 |
1/3/2020 | 1 |
1/4/2020 | 0 |
1/5/2020 | 0 |
1/6/2020 | 1 |
1/7/2020 | 1 |
1/7/2020 | 1 |
1/9/2020 | 1 |
1/10/2020 | 1 |
Solved! Go to Solution.
Try this example
NumberOfBusDays =
VAR _maxdate =
MAX ( 'Date Table'[Date] )
VAR _mindate =
MIN ( 'Date Table'[Date] )
VAR _hiredate =
CALCULATE ( SELECTEDVALUE ( 'Member Table'[Hire Date] ) )
VAR _r =
CALCULATE (
COUNT ( 'Date Table'[Date] ),
'Date Table'[Date] > _hiredate
&& 'Date Table'[Date] < _maxdate,
'Date Table'[IsBusinessDay] = 1
)
// if the hire date is not in the date range, return blank.
VAR _result =
IF ( _hiredate < _mindate || _hiredate > _maxdate, BLANK (), _r )
RETURN
_result
Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂
Thanks! Its close, but I have one issue. Returning BLANK() for the result will not work. It needs to return a sum of the number of business days in that time period. I tried adding SUM('Date Table'[IsBusinessDay]), but it is not totaling correctly and affects the downstream calculations that us that NumberOfBusinessDays measure. For example, Hourly Capacity is 8*NumberofBusinessDays:
Name | NumberOfBusinessDays | Hourly Capacity |
Member 1 | 5 | 40 |
Member 2 | 5 | 40 |
Member 3 | 3 | 24 |
TOTAL | 5 | 40 |
Try this example
NumberOfBusDays =
VAR _maxdate =
MAX ( 'Date Table'[Date] )
VAR _mindate =
MIN ( 'Date Table'[Date] )
VAR _hiredate =
CALCULATE ( SELECTEDVALUE ( 'Member Table'[Hire Date] ) )
VAR _r =
CALCULATE (
COUNT ( 'Date Table'[Date] ),
'Date Table'[Date] > _hiredate
&& 'Date Table'[Date] < _maxdate,
'Date Table'[IsBusinessDay] = 1
)
// if the hire date is not in the date range, return blank.
VAR _result =
IF ( _hiredate < _mindate || _hiredate > _maxdate, BLANK (), _r )
RETURN
_result
Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂
Thanks! Its close, but I have one issue. Returning BLANK() for the result will not work. It needs to return a sum of the number of business days in that time period. I tried adding SUM('Date Table'[IsBusinessDay]), but it is not totaling correctly and affects the downstream calculations that us that NumberOfBusinessDays measure. For example, Hourly Capacity is 8*NumberofBusinessDays:
Name | NumberOfBusinessDays | Hourly Capacity |
Member 1 | 5 | 40 |
Member 2 | 5 | 40 |
Member 3 | 3 | 24 |
TOTAL | 5 | 40 |
Here's the code. It returns the number of business days in any period of time for any one member. If many members are visible in the current context, BLANK is returned since you have not given a definition for the number when this condition is in force.
// Member - dimension with Name (exposed), HireDate (hidden)
// Date - a calendar table in the model marked as such
// [Is Business Day] can be exposed
// Date is NOT connected to Member
[# Member Business Days] =
var __hireDate =
SELECTEDVALUE(
Member[HireDate],
DATE(3000, 1, 1)
)
var __result =
COUNTROWS(
'Date',
KEEPFILTERS( 'Date'[Is Business Day] = 1 ),
KEEPFILTERS( 'Date'[Date] >= __hireDate )
)
RETURN
__result
Best
D
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
19 | |
18 | |
16 | |
15 |
User | Count |
---|---|
51 | |
26 | |
22 | |
17 | |
16 |