Forum Discussion

kathod's avatar
kathod
Regular Visitor
7 years ago

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 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!
Need Help

11 Replies

  • Cmcmahan's avatar
    Cmcmahan
    Resident Rockstar
    7 years ago

    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's avatar
      kathod
      Regular Visitor
      6 years ago

      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's avatar
        Cmcmahan
        Resident Rockstar
        6 years ago

        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.

  • Cmcmahan's avatar
    Cmcmahan
    Resident Rockstar
    6 years ago

    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.