Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jesly_ajin
Helper III
Helper III

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 accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


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 accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


@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 accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


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 accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


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 accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Thanks a lot for the help and support sir.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.