Data Based on Dates

[AMPAllie]

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.

 

[v-juanli-msft]

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

[AMPAllie · Accepted solution]

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 Hours

Daily Hours = [PlannedLaborHrs]/[Net Work Days]

Then Weekly Hours

Weekly Hours = [Daily Hours]*7

Second, 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.