Sum all values per row until limit is reached with multiple criteria

[Anonymous]

Hi All,

This problem really is a beast and has me stuck so let me give some background first...

- Data relates to SAAS sales contracts that can be for any number of months but that are usually <= 36 months

- I am looking at price, start date, end date, length of contracts' term in months, product sku, product category customer account ID, etc. 

- I also have a lot of historical data from contracts that are expired or terminated due to addendums

I am trying to start at the latest end date of a contract and calculate the last 12 months of revenue under that customer ID and under that specific product category. 

Here is my most basic example first before trying a more complex scenario: (link to Power BI Table screenshot below)

https://drive.google.com/open?id=1mQ1De2HcZjpqH8GNepBJpL6AWZMm57gX

In this example and while being filtered on this account id and product category, I need to sum the last 12 months of price. This starts by adding 49,667 + 193,151 = 242,818 but this is only 10.19 months and I need the last 12 months so I then need to go down to the next 3 rows from the previous contract and essentially take the monthly price (by dividing Price by Initial Term) and then multiplying it by (12 months minus 10.19 months) which would result in a total 12-month value since 6/29/2020 of $309,973. 

This example gets me to 12 months just by going through 2 previous contracts but it's possible to need to go back further to get to the past 12 months of revenue (ex: 10.19-month contract 1, 1-month contract 2, 15-month contract 3. This would require adding up a portion of revenue from all 3 contracts.)

How can I do this in Power BI to sum up the price by row until Term reaches 12 months? 

Please let me know if you need more information to understand the issue as I know it can be very confusing. Also, if this problem can be solved then I have additional scenarios that are much more complex that I'd love to get help on as well.

Thanks so much!

-Adam

@Anonymous 

[sturlaws]

Hi @Anonymous,

a file with some example data would really be helpful

[Anonymous]

@sturlaws  - Here is the example data for both power bi and excel from my screenshot. The simplest example of the scenario as already shown if you stay filtered on that account ID, Product Category, and Contract Number but I need this calculation to work without filtering the data. 

Thanks so much for the help!

https://drive.google.com/open?id=1kPW5eAEieDxl5_rZydw0bB-2yW1936gi

https://drive.google.com/open?id=15bAu-qsKEmeGMNZSpHzFCNLVcg9lSkUc

[sturlaws]

A bit tricky this.

How do you want to visualize the 12 month revenue? Is it just latest date pr contract, or do you want to track the development over time?

Or do you want to show it row by row like you have shown in you example?

[Anonymous]

To keep it somewhat simple let's just say we're looking for the last 12 months of revenue starting at the latest end date per Account ID and per Product category.

So, for this example, the top 2 lines are < 12 months so I'd add the revenue from those lines (49,667 & 193,151) to the next most recent product lines (bottom 3 lines with end dates of 8/24/19). However, for these bottom 3 lines, I'd just be looking for a proportion of that revenue that gets me to a total of 12 months. So the math would be (revenue / initial term) * (12 - 10.19) <--(i.e. this is the sum of the initial terms that have already been summed together. In this scenario there is only one contract with a term of 10.19) for each of the bottom three lines separately (math explained: dividing revenue by initial term gets the monthly revenue value and then the second subtraction portion is getting the number of months that are still remaining out of the 12-months total). I could then sum up each of the 5 lines to get the prior 12-month value total as of 6/29/2019. 

Note: I'd need a DO UNTIL function because if the bottom 3 lines were very short, we'll say 1 month long, then I'd need to continue down the column adding in other rows until a 12 month total was reached or if there weren't any more rows to add in. (that being said, I'd also need to add in parameters for this calculation saying that is there is ever a gap period >= 6 months then do not add in that row's revenue into the calculation. 

I know this sounds extremely confusing so please let me know what I can explain better. 

[sturlaws]

Good explanation, still tricky to get it right.

I have not been able to solve this using a measure only, perhaps someone else want to give it a go.

I have made a solution using calculated columns, but this allows for less filtering oportunies than pure measure solution would. Essentially, I calculate revenue pr day for each line, and then find the number of days which are within 12 months/365 days from the latest [Contract end date] in each grouping of [Account ID] and [Product category].

Number of days =
VAR LastEndDate =
    CALCULATE (
        MAX ( data[Contract End Date] );
        FILTER (
            ALL ( data );
            data[Account ID] = EARLIER ( data[Account ID] )
                && data[Product Category] = EARLIER ( data[Product Category] )
        )
    )
VAR LastEndDatePrevYear =
    DATE ( YEAR ( LastEndDate ) - 1; MONTH ( LastEndDate ); DAY ( LastEndDate ) )
RETURN
    SWITCH (
        TRUE ();
        LastEndDatePrevYear < data[Contract End Date]
            && data[Contract Start Date] < LastEndDatePrevYear; DATEDIFF ( LastEndDatePrevYear; data[Contract End Date]; DAY );
        LastEndDatePrevYear < data[Contract End Date]
            && data[Contract Start Date] >= LastEndDatePrevYear; DATEDIFF ( data[Contract Start Date]; data[Contract End Date]; DAY )
    )

[Revenue pr day] =
DIVIDE (
    data[Revenue];
    DATEDIFF ( data[Contract Start Date]; data[Contract End Date]; DAY )
)

and the measure can then be written as

Revenue last 12 months =
CALCULATE (
    SUMX ( data; data[Revenue pr day] * data[Number of days] );
    FILTER (
        ALL ( data );
        data[Account ID] = MAX ( data[Account ID] )
            && data[Product Category] = MAX ( data[Product Category] )
    )
)

[Anonymous]

@sturlaws - I have made a few tweaks to your formulas and I am very close to getting it to work (I think) but I need the DATEDIFF to be more exact. Do you know if there is a way to force the DATEDIFF function to NOT round? Instead of coming up with 310 days, I am looking for a difference of 309.9458333 days.

[sturlaws]

@Anonymous, it as precisise as the input data is, there is 310 days between those two dates. If you need more precision, you have to add time as well.

[Anonymous]

@sturlaws - Here is the last thing keeping my report from working right now. How can I reference a value in an above row? In the attached screenshot link below, I need to have the values for 55 days (highlighted in yellow) to be subtracting out the YearFrac_Remainder in days (circled in red) so that I can have a more accurate view. 

In the column 'Number of Days', the top 2 values are correct and I just need to bottom 3 lines to be 55.00 - .15063 = 54.84937.

Any idea how I can reference that specific cell? 

https://drive.google.com/open?id=1mQ1De2HcZjpqH8GNepBJpL6AWZMm57gX

[sturlaws]

what is you code for the column yearFrac_Remainder?

[Anonymous]

@sturlaws 

YearFrac = IF((vw_RBOB_Final[Initial Term (Months)] / 12) > 1 , 1, (vw_RBOB_Final[Initial Term (Months)] / 12))

YearFrac_Remainder = (1- vw_RBOB_Final[YearFrac])

[sturlaws]

It does not really make any sense to me, this last operation of yours, but here is how you need to do it.

In Power BI there is no way of referencing a cell directly like you would in Excel. You use similar approach as was used in [Number of days]-column: 

number of days NEW =
VAR LastEndDate =
    CALCULATE (
        MAX ( data[Contract End Date] );
        FILTER (
            ALL ( data );
            data[Account ID] = EARLIER ( data[Account ID] )
                && data[Product Category] = EARLIER ( data[Product Category] )
        )
    )
VAR _contract = data[Contract Number]
VAR _product = data[Product Category]
RETURN
    IF (
        data[Contract End Date] < LastEndDate;
        [Number of days]
            - CALCULATE (
                MIN ( data[YearFrac_remainder] );
                FILTER (
                    ALL ( data );
                    data[Contract End Date] = LastEndDate
                        && data[Contract Number] = _contract
                        && data[Product Category] = _product
                )
            );
        [Number of days]
    )

[Anonymous]

@sturlaws  - Thank you so much for all your help! I've put a lot of time into making many improvements and your code is pushing me in the right direction. I am so close and just have one last hurdle that I need to solve with this report that I wanted to see if I could get your help with. 

Currently, my calculations are pulling the last 12 months of revenue starting at the max contract end date filtered by account ID and product category but I also need to make the max date filter relative to specific ID# searches. 

Here's an example to explain:

Account ID               Opportunity ID #                Products             Start Date                 End Date                 Term

1234                             DR1234                           Analytics             1/1/2020                  5/31/2020             5 months

1234                             DR2345                           Analytics             12/1/2018               11/31/2019           12 months

1234                             DR4321                           Analytics              4/1/2018                 11/31/2018           8 months

Currently, the dashboard will look for the max end date of 5/31/2020 and take 5 months of revenue from the top line and 7 months of revenue from the 2nd line to get a total of 12 months. This is good and this calculation should be the default but let's say I do a search for the first line above by filtering on the opportunity ID# DR1234. If I did this then I'd need the first line to be ignored in the calculation, have the max end date be identified as 11/31/2019, and in this example, I'd be able to pull all of the past 12 months of revenue that I am looking for just from the second line alone. 

Another way to say this would be that I need to do the same 12-month calculation but it needs to act relative to a filter/search and begin counting the prior 12 months of revenue, starting on the latest end date of the opportunity ID# prior to the earliest start date of the ID# that I am searching for. 

One last piece to this problem:

The above example is easy since if I searched for DR1234 then the lines with ID# DR1234 would be ignored and the 12-month calculation would start 11/31/2019 and would go back until 12/1/2018. However, one of these lines can only be included in this calculation if the latest end date of the prior ID# is <= 6 months from the earliest start date of the new ID# that is being searched for. So, for example, if I searched for DR1234 and the next soonest end date was 3/1/2019 then the calculation should return $0 for revenue because there were no prior ID#s within the past 6 months. But if the next soonest end date, from DR1234's start date, was 7/1/2019 then that line should be included in the calculation and the 12-month look-back period should start on 7/1/2019 and go back until 6/31/2018. 

I know this is a lot of information and it is very tricky but please let me know if you need more clarification on this final problem that I am trying to solve and again thank you for all your help!

[Anonymous]

@sturlaws 

Here is my example from my last post. I noticed it got messed up. 

[Anonymous]

@sturlaws 

Thanks for your help! This is close but it is still off by $15,897.21. Please let me know if you (or anyone else) has any ideas.