Thanks Paul, the “events in progress” is the phrase I wasn’t familiar with – searching that term gave me some better results than my previous attempts.
I’ve used this type of calculation many times in the past, but this particular application is challenging because the fact table doesn’t include a value for each date that can be summed. I found a thread with a similar problem, and the solution was to modify the table in Power Query instead of using DAX, but it doesn’t actually explain how to do so: Calculate contract value daily - #2 by sam.mckay
I’ve uploaded a copy of my file, with a DAX solution I came up with today that seems to work for the most part. But maybe transforming the table in Power Query would be a better/faster/more stable solution? How would I go about adding a row for each record and for each date between the From Date and the To Date? Again, I’ve searched the forum for an example but haven’t found one yet.
Here’s what I did to solve it (I think) with DAX:
- Added a column to change the 12/31/3000 end dates to the max date in my date table (12/31/2020)
- Added a column to convert the annual goal to a daily goal
- Created a measure for Workdays (excludes weekends and holidays per the holiday table in the model)
- Used the events in progress structure to create a measure for the Active Daily Goal Hours
- Used the events in progress structure to create a measure for the Active Workdays, utilizing a variable that calculates the dates between the MAX( [Goal Start Date], [First Date in Slicer] ) and the MIN( [Goal End Date], [Last Date in Slicer])
- Multiplied the two measures above, wrapped in SUMX( ALLSELECTED( [Goal Table] ) ) so that it responds to the slicers correctly
I think this does the job, and it seems to perform quickly enough, so maybe doing the Power Query transformation isn’t necessary unless I need to get into some more complex table visuals.
Billing Goal Demo.pbix (669.7 KB)