Semi-additive max date by account and category

[aggiebrown]

Hi all,

I am working with data which captures stages of clients from prospect to customer. each stage records a different potential contract value of the customer, and the latest date for particular customer holds the most accurate value I want to be able to extract by date/month etc.

Link to dummy data in PBI for context:

https://1drv.ms/u/s!AoIOEd5cDuqQuRmioxhdXlsHT3pX?e=72Nwue

This is the sample data screenshot and desired output (count total max value of opp based on the last date the opp has been modified and so on for the count of opps based on stage and forecast category)

I tried using Semi-Additive Measure to calculate sum for Last date of the day, but it only sums max opp_1 and max_date - It does not add multiple opp values to create pipeline of prospects&customers based on any given time period (month date) - see below 

Any help would be appreciated. 🙂 

[tamerj1 · Accepted solution]

Hi @aggiebrown 
Please refer to attached sample file with the solution

[tamerj1]

@aggiebrown 
What makes this complicated is having correct total. In other words to include the newly added values in the total. Other thing that I have to mention is that for example the values for Coffee (Jan. and Apr. ) are added as long as Coffee is part of the selected OPPs.

[aggiebrown]

Hi again, 

I can see the limitation. What you've done works, but the April 2020 value should be blank/null. Once the opp is booked (stage = booked) it should no longer calc it. March should be the last month with the value.

Here is the desired end result for Coffee Shop. Is there a way to filter the main measure to stop adding values based on stage name being Booked?

[tamerj1]

@aggiebrown 

* Last Value of the day INDIVIDUAL OPP LEVEL = 
VAR LastDateWithData = 
    CALCULATE ( MAX ( fact_opps[Date] ), ALL ( date_table ) )
VAR Months =
    VALUES ( date_table[calendar_year_month_number] )
VAR OPPs =
    CALCULATETABLE ( VALUES ( fact_opps[Opp_ID] ), ALL ( date_table ) )
VAR SummaryTable =
    CROSSJOIN ( Months, OPPs )
VAR Result =
    SUMX (
        SummaryTable,
        VAR CurrentMonthNumber =
            VALUE ( date_table[calendar_year_month_number] )
        VAR CurrentDate = 
            CALCULATE ( MAX ( date_table[date] ) )
        VAR CurrentMaxDate =
            CALCULATE ( MAX ( fact_opps[Date] ) )
        VAR PreviousMaxDate =
            CALCULATE (
                MAX ( fact_opps[Date] ),
                VALUE ( date_table[calendar_year_month_number] ) = CurrentMonthNumber - 1,
                ALL ( date_table )
            )
        VAR CurrentACV =
            CALCULATE ( SUM ( fact_opps[ACV] ), fact_opps[Date] = CurrentMaxDate )
        VAR PreviousACV =
            CALCULATE (
                SUM ( fact_opps[ACV] ),
                fact_opps[Date] = PreviousMaxDate,
                ALL ( date_table )
            )
        RETURN
            IF ( 
                CurrentDate <= LastDateWithData,
                COALESCE ( CurrentACV, PreviousACV )
            )
    )
RETURN
    Result

[aggiebrown]

@tamerj1 

Thanks again. I've extrended the data table to make sure it works and when expanding it, the condition does not seem to work.

Because the measure is looking at Month and adding one only, if the stage changes happened further apart than a month then the running pipeline value is not working (January 2020 showing blank even though it should carry over 2130.00) Also the pipeline should stop running only if the stage = booked (Coffee Shop example works), but if the Opp is not booked then it should forecast that value for as long as the calendar goes, e.g. Bakery Pipeline by month should continue to show 2250.00 in May, June, July and do on, until it is Booked.

I've uploaded the updated Dummy Data to this link: 

Dummy Data.pbix

thank you in advance.

[aggiebrown]

@tamerj1  Thank you so much! 

Coffee Shop has that example in dummy data where January ACV should be showing as whatever was in December (1823.00) 

So a condition would be if the Stage <> Booked then give me the latest value from previous date. Once it's booked that's the last value stamp against the date intelligence. 

 

[amitchandak]

@aggiebrown , These can help

Power BI closingbalancemonth closingbalancequarter closingbalanceyear, lastnonblankvalue- Closing Balance, Distibutre Target: https://youtu.be/yPQ9UV37LOU

OR

Latest
https://amitchandak.medium.com/power-bi-get-the-last-latest-value-of-a-category-d0cf2fcf92d0

https://amitchandak.medium.com/power-bi-get-the-sum-of-the-last-latest-value-of-a-category-f1c839ee8...