Forum Discussion
Data Based on Dates
I have a dataset that is "Planned labor Hours" for a date range (start and end date). I have created columns that calculate planned hours per workday and planned hours per week.
What I need to accomplish is a result that is a Matrix with week date ranges as column headers (or just the Start date of the week), and number of hours per week as the values. I have a calendar table that includes a "Start of week" date.
First, to answer your question:
To calculate those fileds was a multistep process. First I had to create a column for the number of workdays in the date range:
Net Work Days = CALCULATE(SUM(Dates[IsWorkday]),DATESBETWEEN(Dates[Date],RPAssignment[StartDate],RPAssignment[EndDate] ))Then Daily HoursDaily Hours = [PlannedLaborHrs]/[Net Work Days]Then Weekly HoursWeekly Hours = [Daily Hours]*7Second, I found a long, tedious, round-about way to accomplish what i want. Then it turned out it's not what the client wanted. So i am marking this solved.
2 Replies
Hi AMPAllie
How do you create columns that calculate planned hours per workday and planned hours per week?
From the screenshot, "Planned labor Hours" calculate for many weeks and workdays. also, it overlapping with dates for the third and fourth rows in the screeshot.
How do i allocate the "Planned labor Hours" for week date ranges in your matrix?
Best Regards
maggie
First, to answer your question:
To calculate those fileds was a multistep process. First I had to create a column for the number of workdays in the date range:
Net Work Days = CALCULATE(SUM(Dates[IsWorkday]),DATESBETWEEN(Dates[Date],RPAssignment[StartDate],RPAssignment[EndDate] ))Then Daily HoursDaily Hours = [PlannedLaborHrs]/[Net Work Days]Then Weekly HoursWeekly Hours = [Daily Hours]*7Second, I found a long, tedious, round-about way to accomplish what i want. Then it turned out it's not what the client wanted. So i am marking this solved.
