cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
msimmonsmcse
Helper I
Helper I

Counting dates in a filtered date range

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

 

2 ACCEPTED SOLUTIONS
nandukrishnavs
Super User II
Super User II

@msimmonsmcse 

 

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
🙂

View solution in original post

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:

 

NameNumberOfBusinessDaysHourly Capacity
Member 1540
Member 2540
Member 3324
TOTAL540

View solution in original post

4 REPLIES 4
nandukrishnavs
Super User II
Super User II

@msimmonsmcse 

 

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
🙂

View solution in original post

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:

 

NameNumberOfBusinessDaysHourly Capacity
Member 1540
Member 2540
Member 3324
TOTAL540

View solution in original post

Anonymous
Not applicable

You have to state exactly what you want to see for the totals. I guess you'd like to sum up the numbers of business days across all visible names and the hourly capacity should be the sum of capacities for each individual member. Right?

Best
D
Anonymous
Not applicable

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

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

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors