Allocate value evenly between start and end day excluding weekends and holidays

[omelo]

Hi all,

I have been reading all posts, watching all videos I came across (there are a lot!) on this topic. Thank you all. It is a great content and have learned a lot!

I do have a datset containing:

Employee, project number, start, end date, hoursEstimate

I also have a calendar table, no relationship to data table and have a holiday table with date column

I have a matrix visual, my columns are year,month,day

Rows are employee,project.

Following this great post How to divide/distribute values between start date... - Microsoft Power BI Community 

I'm able to allocate the value across the start and end date 

The DAX is:

Value by day of Month = CALCULATE(SUMX(SUMMARIZE(FILTER(CROSSJOIN(' Workload','Date'),'Date'[Date]>='Workload'[StartDate]

&& 'Date'[Date]<='Workload'[Due date]),' Workload'[ID.1], 'Date'[Date],' Workload'[HoursEstimate],'Workload'[StartDate],' Workload'[Due date]),

DIVIDE('Workload'[HoursEstimate],DATEDIFF('Workload'[StartDate],'Workload'[Due Date],day)+1)))

Since I need to exclude weekends and holidays,  I've created a workday column that shows 1 for weekdays, 0 for saturday and sunday.
I have created a holiday table with a date column.

I then, attempted to modify the above DAX, also try creating separate measures but I'm not having any luck!

I would appreciate if someone can help me either modifying the dax measure above, or suggesting me a different way to allocate hoursestimate  evenly between start and end date excluding weekends and holidays.

I will be very grateful!

[v-henryk-mstf]

Hi @omelo ,

The created formulas don't seem to have much of a problem. What does your data model look like and are there filtering issues due to table relationships.

Looking forward to your feedback.

Best Regards,
Henry

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

[omelo]

Hi @amitchandak , as I mentioned, I do have a "workday" column that gives me 0 for saturday and Sunday and 1 for weekday. I'll deal with the holidays later>
I've tried modifyng the dax but I'm overcomplicating the expresion and don't get the expected results, could you please help me with this expression.

VAlue by day of Month = CALCULATE(SUMX(SUMMARIZE(FILTER(GENERATE(' Workload-Test','Date'),'Date'[Date]>='Workload-Test'[TaskStartDate]

&& 'Date'[Date]<='Workload-Test'[Due date]),'Workload-Test'[ID.1], 'Date'[Date],'Workload-Test'[HoursEstimate],'Workload-Test'[TaskStartDate],'Workload-Test'[Due date]),

DIVIDE('BC EDR Workload-Test'[HoursEstimate],(('BC EDR Workload-Test'[Due date]-' Workload-Test'[TaskStartDate]-SUMX

(Filter('Date',

'Date'[Date]>'Workload-Test'[TaskStartDate]&&

'Date'[Date]<'Workload-Test'[Due Date]), IF(WEEKDAY('Date'[Date])=1||WEEKDAY('Date'[Date])=7,1,0))+1

)))

))

Thank you for your help

[amitchandak]

@omelo , Have  date calendar with holiday day .

Then you can follow the approach given with calendar to get business date, How to calculate Business Days/ Workdays, with or without date table: https://youtu.be/Qv4wT8_P-AA

In divide

Crossjoin approach seems fine. You can use generate in place of crossjoin

https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-o...