Forum Discussion

ctedesco3307's avatar
ctedesco3307
Resolver II
4 years ago
Solved

Date Calculations

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

Need Help
  • d_gosbell's avatar
    d_gosbell
    4 years ago

    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

     

     

2 Replies

  • d_gosbell's avatar
    d_gosbell
    Super User
    4 years ago

    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

     

     

  • ctedesco3307's avatar
    ctedesco3307
    Resolver II
    4 years ago

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