How do I count the number of days within a slicer data range?

[lcaputo]

Hi,

I am trying to the sum of days between the dates selected in a slicer.  This calculation should exclude weekends.

The data type of the field used in the slicer is date/time:

timeStart

I created a new column to claculate whether it is a business day:

IsWorkday = SWITCH(WEEKDAY('Time Entries'[timeStart].[Day]),1,0,7,0,1)

And I created a 'Test' measure as follows:

Test =

VAR FirstDay = CALCULATE(

MIN('Time Entries'[timeStart].[Date]),

ALLSELECTED('Time Entries'[timeStart].[Date])

)

VAR LastDay = CALCULATE(

MAX('Time Entries'[timeStart].[Date]),

ALLSELECTED('Time Entries'[timeStart].[Date])

)

RETURN

CALCULATE(

SUM('Time Entries'[timeStart].[Date]),

DATESBETWEEN('Time Entries'[timeStart].[Date],FirstDay,LastDay)

)

However, I believe the function 'DATESBETWEEN' is not support for 'date/time' data types (only supported for 'date' data types).

Does anyone have recommendations for getting the calculation to work for date/time, or have a better recommendation to achieve this?

Thank you!

[aj1973]

Hi @lcaputo 

DATESBETWEEN is a time intelligence function and only works well and reliably when you have a Calendar date table. So I would go with @FrankAT solution. 

Attached a sample Pbix where I also excluded holidays to get the number of working days.

https://drive.google.com/file/d/1bikJXNbDHW4k-D1rGDXCWlUTPYTuHgtD/view?usp=sharing

[FrankAT]

Hi @lcaputo ,

give this a try:

Sum of Workdays = SUM('Table'[IsWorkday])


Count of Workdays = 
CALCULATE (
    [Sum of Workdays],
    FILTER (
        'Table',
        'Table'[Date] >= MIN ( 'Table'[Date] )
            && 'Table'[Date] <= MAX ( 'Table'[Date] )
    )
)

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

[lcaputo]

Hi @FrankAT 

Thanks for your help! I created the "Sum of Workdays" and "Count of Workdays" measures you provided, but there are multiple rows in my table with the same day. For example:

Therefore, when the slicer date range is set like so, it counts 8 instead of 3.

Any suggestions?

[FrankAT]

Hi @lcaputo ,

column IsWorkday is part of the calendar table. You are looking for a different solution: Distinctcount of workdays between selected dates in the slicer. Can you provide some sample data?

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

[lcaputo · Accepted solution]

@FrankAT 

Thanks for lending your help again. Much appreciated.

I was able to get a distinct count of days by:

Number of Distinct Days = CALCULATE(DISTINCTCOUNT('Time Entries'[Start Date]),all('Time Entries'[Start Date]))

The number of days that I am expecting are displaying correctly now.

[Migasuke]

Hi @lcaputo 

I have adjusted the measure a bit and applied for mine demo data. Take a look:

Test =

VAR FirstDay = CALCULATE(

MIN(Between[Date]),

ALLSELECTED(Between[Date])

)

VAR LastDay = CALCULATE(

MAX(Between[Date])

)

RETURN

CALCULATE(

COUNT(Between[Date]),

DATESBETWEEN(Between[Date],FirstDay,LastDay)

)

The main difference is, that I have COUNT instead of SUM + I dont use .[Date].

Measure is functional with Dates/Time:

Let me know how this helped!