Forum Discussion
Distributing multiple values over different time periods
Hello, I am working with contracts that have a total value and an end date. I want to spread the total value on each working day between today and the end date. So far I calculated the daily value ...
So I was confused when you said you didn't know how to spread the result over multiple days. Until I attempted to create a mocked up a proof of concept.
Turns out it was trickier than I thought. I was able to accomplish it with a few measures.
Working Days Remaining = CALCULATE( COUNTROWS( ALLSELECTED('Calendar')), DATESBETWEEN( 'Calendar'[Date], TODAY(), SELECTEDVALUE( Contracts[End Date])), 'Calendar'[IsWeekDay] = 1 )
Per Day Amt = DIVIDE( SELECTEDVALUE(Contracts[Value]), [Working Days Remaining])
Sum of Active Contracts = SUMX( CALCULATETABLE( VALUES(Contracts[ContractID]), FILTER( Contracts, Contracts[Start Date] <= SELECTEDVALUE('Calendar'[Date]) && Contracts[End Date] >= SELECTEDVALUE('Calendar'[Date]))), [Per Day Amt])
I then put [Sum of Active Contracts] into a line graph with date as the X-axis. I've attached a sample .pbix that shows all the pieces in action. There's probably a more efficient way of doing this, but this is the first correct answer I was able to create.