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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.