Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
Thank you so much for your quick reply!! I will try this out this morning! This is super - helpful.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |