Date Calculations

[ctedesco3307]

Hi All - looking for design suggestions on how to calculate this:

I have a table of Plans that have a start and end dates. The end goal is to create a report that shows the hours for each plan by Week.

The PlanDate table has
PlanID
PlanStartDate
PlanIDEndDate
HoursPerDay

The start date and end dates vary by plan and can span anywhere from a day to a few months

The calculation need to be if PlanStartDate_from is < (ThisDay) and PlanIDEndDate >(ThisDay) then HoursPerDay else 0 (or blank)

The end goal is to build a report with the plans in the rows and the dates on the columns for each week to show the weekly planned hours by planID

So I know I need a Date table using the CALENDAR function to generate the days and then I need to pull in the plan ID and the evaluation on of that plan start date and plan end date fall in that day range.  

I hope this makes sense!  All help welcome 

Thanks in advance

[ctedesco3307]

Thank you so much for your quick reply!! I will try this out this morning!  This is super - helpful. 

[d_gosbell · Accepted solution]

So you should be able to do this with an expression like the following using a separate date table. 

Plan Hours = 
SUMX(PlanDate,
    var currentStart = PlanDate[PlanStartDate].[Date]
    var currentEnd = PlanDate[PlanEndDate].[Date]
    return SUMX(FILTER( values( 'Date'[Date]), 'Date'[Date] >= currentStart && 'Date'[Date] <= currentEnd), PlanDate[HoursPerDay])
)

Below is a screenshot showing the sample data I entered on the left and the output from the calc on the right. I've also attached the pbix so you can have a play with it yourself