cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jesly_ajin
Helper II
Helper II

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

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

 

1 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!!


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

View solution in original post

8 REPLIES 8
Jihwan_Kim
Super User
Super User

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!!

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

@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.

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!!


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

View solution in original post

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.

 


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

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

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

I sent my email address via message.

please check your message inbox.

Thank you.


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

Thanks a lot for the help and support sir.

Helpful resources

Announcements
Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Microsoft Ignite 768x460.png

Find your focus

Explore the latest tools,training sessions,technical expertise, networking and more.

Power BI Womens Summit 2021 768 x460.jpg

Interviews, learning sessions, allies, and more!

#PowerBIWomenSummit

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!