Count the days between the slicer dates in Power BI excluding weekends

[jesly_ajin]

Count the days between the slicer dates in Power BI excluding weekends

[Jihwan_Kim]

Hi, @jesly_ajin 

I am not sure about how your data model look like, but in my cases, I generally have custom date table, and I normally write below DAX measure.

I have DayOfWeek column in my date-table. (Sunday = 0, Saturday = 6)

count days between slicer dates excluding weekends =

CALCULATE (

COUNTROWS ( dates ),

FILTER ( ALLSELECTED ( dates ), dates[DayOfWeek] <> 0 && dates[DayOfWeek] <> 6 )

)

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!

[jesly_ajin]

@Jihwan_Kim , 

Thanks for response.

I have 2 measures [CalcFromDate] & [CalcToDate] :-

CalcFromDate = IF(MIN(InEightProjEquipmentStagingTable[FROMDATE])<=[MinSelectedDate],[MinSelectedDate], MIN(InEightProjEquipmentStagingTable[FROMDATE]))

CalcToDate = IF(MAX(InEightProjEquipmentStagingTable[TODATE])>=[MaxSelectedDate],[MaxSelectedDate], MAX(InEightProjEquipmentStagingTable[TODATE]))

ActualAllocEqDays =

SUMX(InEightProjEquipmentStagingTable,

IF(DATEDIFF([CalcFromDate],[CalcToDate],DAY)+1<=0,0,DATEDIFF([CalcFromDate],[CalcToDate],DAY)+1))

Now, in ActualAllocEqDays, it takes all the days including weekends.

I need the count of days excluding weekend.

Please help.

[Jihwan_Kim · Accepted solution]

Hi, @jesly_ajin 

I am not quite sure if the below measure suits your requirement because it was quite difficult for me to understand your data model.

Please try to test it on the blank page.

On the blank page, create a slicer that uses a calendar-table, that is disconnected to the table.

weekdayscount(new) =
VAR newtable =
ADDCOLUMNS (
InEightProjEquipmentStagingTable,
"@dayscount",
CALCULATE (
COUNTROWS ( 'calendar' ),
DATESBETWEEN (
'calendar'[Calendar_date],
SELECTEDVALUE ( InEightProjEquipmentStagingTable[FROMDATE] ),
SELECTEDVALUE ( InEightProjEquipmentStagingTable[TODATE] )
),
'calendar'[Week_day_name] <> "Saturday"
&& 'calendar'[Week_day_name] <> "Sunday"
)
)
RETURN
SUMX ( newtable, [@dayscount] )

If the above is not working, please try to change the [FROMDATE] and [TODATE] columns to Date Type, not date & time type.

I hope it works.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!

[Jihwan_Kim]

Hi, @jesly_ajin 

Thank you for your question.

If it is OK with you, can I see your sample pbix file? 

It is quite difficult for me to come up with a desirable solution without seeing the data model.

By only seeing the first two measures, I think those are referencing the date-table to calculate the days-count between the slicer. -> is it not connected?

If it is not connected, I think it is not so easy to put the condition (noweeken) that comes from the date-table.

If it is OK, please share the sample pbix file, then I can try to solve it.

Thank you very much.

[jesly_ajin]

it is not connected. It is like calculating the difference between 2 independent dates.

[jesly_ajin]

Kindly send me your email id - i can do the wetransfer of the file to you.

[Jihwan_Kim]

I sent my email address via message.

please check your message inbox.

Thank you.

[jesly_ajin]

Thanks a lot for the help and support sir.