cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
navedkhan Regular Visitor
Regular Visitor

Calculate Total No. of Working Days (in a calendar year or months) by way of measure

Hi  All - i need DAX Measure which can add up total number of Working Days when date range is selected in a slicer in PowerBi?

 

It has to be a measure !! Suppose date range selected is 10-Jan-2019 till 30-May-19, i want to know sum of working days between these two dates.

 

Any help would be helpful. Anyone please?

 

@Zubair_Muhammad 

 

@TomMartens 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Calculate Total No. of Working Days (in a calendar year or months) by way of measure

Hey @navedkhan 

 

I guess this will do the trick:

Number of WorkDays = 
var weekdays = {1, 2, 3, 4, 5}
return
SUMX(
    VALUES('Table1'[Date])
    , var isWeekDay = IF(WEEKDAY([Date],2) IN weekdays, 1, BLANK())
    return
    isWeekDay
)

From a personal point of view, I would not create a calculated column as there are repeated dates because your table is not a dedictated Calendar table, but this is just my personal thinking. And even a dedicated Calendar table would be of no help, as your requirement is to calculate the workingdays in your "fact" table.

 

Hopefully this is what you are looking for.

 

Regards,

Tom

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!

View solution in original post

8 REPLIES 8
jthomson New Contributor
New Contributor

Re: Calculate Total No. of Working Days (in a calendar year or months) by way of measure

Make some sort of column in your date table that highlights if a date is a Saturday/Sunday, and count the number of rows that aren't flagged. Can do similar with public holidays by making a list of when they are and joining it to your calendar table

Highlighted
JIGAR Regular Visitor
Regular Visitor

Re: Calculate Total No. of Working Days (in a calendar year or months) by way of measure

@navedkhan 

 

You can achieve this create a calculated column first. Below is the DAX.

 

IsWeekDay = if(WEEKDAY(DimDate[Date],2) <=5, 1,0)
 
After creating this column, you will have to create a measure. Below is the DAX
 
NoofWorking Days = CALCULATE(SUM(DimDate[IsWeekDay]))
 
Refer the screenshot below for a working sample.
 
Note - I have not taken public holidays into consideration here.
Capture.PNG

 

Hope this helps !!

 

Please mark the solution as accepted if it was appropriate.

 

Thanks

navedkhan Regular Visitor
Regular Visitor

Re: Calculate Total No. of Working Days (in a calendar year or months) by way of measure

@JIGAR 

thanks for your guidance. i applied the steps as mentioned but am not getting exactly what you showed or i wanted;

 

after creating the measure am getting wierd totals between 30-Nov-18 till 31-May-19 am getting No. of Working Days=5609234 days which is incorrect.

 

Please note that i've only one Date column and those dates are repeated in various rows as it captures per entry of employees who have entered a premise. so i guess that's why its adding up and inflating actual no. of working days.

 

pls can you help resolve this further? and what to do to ignore Public Holidays?

vivran22 Member
Member

Re: Calculate Total No. of Working Days (in a calendar year or months) by way of measure

@navedkhan 

 

Hi,

 

Please try this:

https://community.powerbi.com/t5/Desktop/equivalent-function-of-Networkdays-in-DAX/td-p/245103

 

Thanks,

Vivek

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

bidevsugmen Regular Visitor
Regular Visitor

Re: Calculate Total No. of Working Days (in a calendar year or months) by way of measure

Hi @navedkhan 

 

 

Can you try using DISTINCTCOUNT in your measure after creating the calculated column IsWeekDay as mentioned in the previous replies?

 

For example: CALCULATE(DISTINCTCOUNT(Table[IsWeekDay]))

 

Hope this helps!

 

Regards,

Suguna.

navedkhan Regular Visitor
Regular Visitor

Re: Calculate Total No. of Working Days (in a calendar year or months) by way of measure

No DISTINCTCOUNT doesn't work at all.... 

 

1) one column for Dates

2) when slicer is moved to and fro

 

i need to calculate total no. of working days between to and fro slicer selection. 

Pls suggest simple MEASURE!!

Super User
Super User

Re: Calculate Total No. of Working Days (in a calendar year or months) by way of measure

Hey @navedkhan 

 

I guess this will do the trick:

Number of WorkDays = 
var weekdays = {1, 2, 3, 4, 5}
return
SUMX(
    VALUES('Table1'[Date])
    , var isWeekDay = IF(WEEKDAY([Date],2) IN weekdays, 1, BLANK())
    return
    isWeekDay
)

From a personal point of view, I would not create a calculated column as there are repeated dates because your table is not a dedictated Calendar table, but this is just my personal thinking. And even a dedicated Calendar table would be of no help, as your requirement is to calculate the workingdays in your "fact" table.

 

Hopefully this is what you are looking for.

 

Regards,

Tom

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!

View solution in original post

navedkhan Regular Visitor
Regular Visitor

Re: Calculate Total No. of Working Days (in a calendar year or months) by way of measure

that worked fantastically. many thanks @TomMartens 

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 116 members 1,334 guests
Please welcome our newest community members: