Distributing multiple values over different time periods

[kathod]

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 of each contract, but I am stuck when it comes to calculating each day`s value (all contracts together).

The contract table looks basically like this:

customer no., contract no., end date, total value

1, 123, 31.08.2019, 20k

2, 456, 31.12.2020, 50k

3, 556, 30.09.2019, 5k

1, 448, 31.01.2020, 10k

Each total value should be spread evenly among the days between today() and the end date.

e.g. 20k shall be spread 1,67k (20k/12days) for each day from 20.08. to 31.08.2019

5k shall be spread 0,12k (5k/42days) for each day from 20.08. to 30.09.2019

Finally I want to have the summed daily value available.

I am working with a time table and I am working with a working days table where each working day is marked with 1 (and 0 for non-working days). I am using current offset for working days.

Calculating the daily value of each contract I have done with this formula:

ExpWD Rev = if('offene Kontrakte_BI'[Gültig bis] > today(); CALCULATE(SUM('offene Kontrakte_BI'[Contracts EUR]))/CALCULATE(SUM('Calendar_working days_BI'[CurWDoffset]));0)

Thank you for your help!

[Cmcmahan]

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.

[Cmcmahan]

How is the first date determined? Is it just TODAY()?  It should be easier to get your result by just counting the working days between now and the end date and using that as a divisor.

Assuming you put this measure in a table split up by Contract No for context, something like this should work for both individual rows and the total. My apologies for not using the table/column names you provided, but my German is very poor.

Daily Amt =
SUMX (
    SUMMARIZE (
        Table1,
        Table1[Contract No],
        "Daily Total", DIVIDE (
            SUM ( [Total Value] ),
            CALCULATE (
                COUNTROWS ( 'Calendar' ),
                DATESBETWEEN ( 'Calendar'[Date], TODAY (), LASTDATE ( Table1[End Date] ) ),
                'Calendar'[IsWorkingDay] = 1
            )
        )
    ),
    [Daily Total]
)

[kathod]

Hello Cmcmahan,

Thank you for your answer.

If I got it right, the measure you provided calculates the expected daily revenue by dividing the total amount by working days.

Maybe I am missing something in your post, but how can I distribute the expected daily revenue among the future working days? At the end I would like to see, that we can expect amount X during (rest of) August, amount Y during September, etc.

Therefore I think it is necessary to distribute the expected daily revenue throughout the future days until the end date is reached. e.g. to calculate the amount for 22.08.2019 Power BI should add up all expected daily revenues with end date >= 22.08.2019.

This is your measure with my data:

Daily Amt = 
SUMX (
    SUMMARIZE (
        'offene Kontrakte_BI';
        'offene Kontrakte_BI'[Verkaufsb.];
        "Daily Total"; DIVIDE (
            SUM ( 'offene Kontrakte_BI'[Contracts EUR]);
            CALCULATE (
                COUNTROWS ( 'Calendar_working days_BI' );
                DATESBETWEEN ( 'Calendar_working days_BI'[Date]; TODAY (); LASTDATE ( 'offene Kontrakte_BI'[Gültig bis] ) );
                'Calendar_working days_BI'[WD] = 1
            )
        )
    );
    [Daily Total]
)

This is the result as a graph:

[Cmcmahan]

Would each day not have the same amount?  I think I'm missing something in what you're asking.

From what I understand, you have $X as the contract amount.  I calculate Y working days between now and the contract end date.  I divide $X by Y days to find the amount per day.  If you want to figure out the total revenue for a month (let's say September), just multiply by Z working days in September. So $X/Y days * Z days would get you your total expected dollar amount for September.

Otherwise, I may need you to explain the whole distribution of funds thing, since it seems like a very abstract concept right now.

[kathod]

Maybe I am thinking too complicated - I was planning to realise the distribution in a calculated column.

Your suggestion seems to be a measure. I agree that  $X/Y days * Z days gives me the right amount, if X is valid for all days of Z.

At this point I am struggling. How to tell Power BI to take only those daily values and multiply them by future working days, where the contract is still valid?

[Cmcmahan]

It seems super strange to me to set this up as a calculated column.  Would you have the same contract number in multiple rows, along with every working date between start and end of contract, and the expected amount?  That seems like a stupidly large amount of data replication.

When you say "if X is valid for all days of Z", how can you tell whether this is true for a given day and contract?

Would it make more sense with your data to take the contract start date instead of TODAY() as the first date, and then do the same math with that as a starting point?

[kathod]

If the solution is a measure or a calculated column does not matter to me.

Does it help to take the contract start date? At the end I will filter on future days anyway.

Do you see the point where I am struggling? I need to make sure that the daily values are considered only for those days, where the date of the day is before (or on) the contract expiry date.

e.g. having a contract with total value 12k and expiry date 13.09.2019 -> daily value (future working days only) = 1k and now I want Power BI to consider this 1k for every day until expiry date; at the end I want to see, that the distributed contract value for August is 2k and for September it is 10k

[Cmcmahan]

Most of what you're saying makes complete sense to me. 

Just to recap what we can currently do and your goals:

• Currently, you can calculate the per day value of a contract
  • This is done by taking the total value of a contract and dividing it by the number of working days between TODAY() and the contract end date
  • ^ this is the part that doesn't make total sense to me.  Is the total value of a contract updated daily? See my example below that explains my confusion.
• You can also count the working days in a given timeframe with your time dimension
• You have the option take the per day value of a contract and multiply it by the number of working days in a given timeframe
  • This could be a single month, a week, now until the end of the contract, etc

I guess what I'm not understanding is why you're using TODAY() to calculate the remaining value of a contract.  Please correct me, but I would assume the value of Contract LMNO is constant.  If contract LMNO is a week long with a worth of $10,000, there would be 5 working days between contract start and end dates to split the $10,000. 

However, if you use the number of working days between TODAY() and the contract end date, on the first day of the project, each day would show $2000, but the 2nd day there would only be 4 working days between the new TODAY() and the end date, meaning each day has a value of $2500, even though the contract value hasn't changed.

If the remaining value of a contract is updated daily, please let me know and ignore this whole section, as your setup makes much more sense if that's the case.

Finally, how do you want all of this displayed in a visual?  It seems that you want to create a timeline as your X-axis, and sum up the per day value of all active contracts. If this is not correct and you want to display this in some sort of table visual, please let me know.

[kathod]

Sorry for not clarifying this earlier - the contracts are indeed updated daily. So the value of a contract is only showing the open amount that we can expect in the future.

e.g. we do a contract with total value 12k with expiry x. As the time goes by, the customer orders goods that are covered by the contract and each order is reducing the total value of the contract. So after some time the value of the contract is down to 5k for example. Those 5k represent the value that the customer still has to order before expiry.

The visualisation is planned like you described it. Having a timeline and showing the distributed values along.

[Cmcmahan]

So then yeah. I think in that case that the previously proposed solution should work.  Is it giving you bad results?

[kathod]

Calculating the daily value of the contracts for the upcoming working days until expiry is working.

But I still can`t figure out how to distribute these values to future dates.