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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

How to calculate business days

I have one date universal date table that is from 1900-01-01 to 9999-12-31. And have one businee day flag.

Business day flag is 1 when we don't have holiday and 0 when we have holidays.

 

I have one  table which has [acno date] & [received date] and which is not in relationship with date table.

So I want to calculated number of business days between [acno date] & [received date].

 

For e.g

[acno date] =2019-01-01  & [received date]=2019-01-31

so total calender days=31 and business days=31-8=23

where for 8 =days(weekends) business flag is 0.  so we worked for 23 days only.

Is there any way we can calculate business days for each [acno date] & [received date]

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

@Anonymous please try this measure and check if it works for you

 

Measure = 
VAR _acno = MAX('Table'[acno date])
VAR _received = MAX('Table'[received date])
VAR _holidays = CALCULATE(COUNT('Calendar'[Date]),FILTER('Calendar','Calendar'[Date]>=_acno && 'Calendar'[Date]<=_received&&'Calendar'[Flag]=0))
VAR diff = DATEDIFF(_acno,_received,DAY)
RETURN diff-_holidays

 

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

@Anonymous please try this measure and check if it works for you

 

Measure = 
VAR _acno = MAX('Table'[acno date])
VAR _received = MAX('Table'[received date])
VAR _holidays = CALCULATE(COUNT('Calendar'[Date]),FILTER('Calendar','Calendar'[Date]>=_acno && 'Calendar'[Date]<=_received&&'Calendar'[Flag]=0))
VAR diff = DATEDIFF(_acno,_received,DAY)
RETURN diff-_holidays

 

Anonymous
Not applicable

it will give you same output. Acno_date and received_Date are columns having multiple values.

e.g

Acno_date|received_Date|Business day

2019-01-01|2019-01-15| 11

2019-07-01|2019-01-15| 6

2019-01-01|2019-01-31| 23

...

...

...

... so on

Anonymous
Not applicable

@Anonymous Why do you think It will produce same output. Please refer below picture. I have created a flag in calendar table which returns zero for holidays and 1 for working days

Then I have another table in which I have acno and received date. Please let me know if this is not the expected result.

datediffwithholiday.png

Anonymous
Not applicable

@Anonymous  how it can be done with column? not measure

Anonymous
Not applicable

@Anonymous Please create a calculated column as per below

WorkingDays = 
VAR _holidays = CALCULATE(COUNT('Calendar'[Date]),FILTER('Calendar','Calendar'[Date]>='Table'[Acno] && 'Calendar'[Date]<='Table'[Received]&&'Calendar'[Flag]=0))
VAR _days = DATEDIFF('Table'[Acno],'Table'[Received],DAY)
RETURN _days-_holidays

Please let me know if you have any question. if it helps feel free to hit kudos button.

Anonymous
Not applicable

sorry i haven't added that condition. Thanks for help.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.