Cumulative What if Parameter

[enedella]

I am trying to create a what if scenario and make the amounts cumulative over each year.

There is a slicer to choose what contract year the adjusted amount should start in and then a what-if parameter that has a slider to choose the adjusted $ amount. So for this example, the CY would be CY2 and the slider would have -$100.

My current formula to get the adjustment to start in the year that is selected from the slicer is:

Adj Amount cumulative =

IF(VALUES('GP Invoices SCENARIOS'[CY]) = "CY1",'$ Spend Cumulative'[$ Spend Cumulative Value],

IF(VALUES('GP Invoices SCENARIOS'[CY]) = "CY2",'$ Spend Cumulative'[$ Spend Cumulative Value],

IF(VALUES('GP Invoices SCENARIOS'[CY]) = "CY3",'$ Spend Cumulative'[$ Spend Cumulative Value],

IF(VALUES('GP Invoices SCENARIOS'[CY]) = "CY4",'$ Spend Cumulative'[$ Spend Cumulative Value],

IF(VALUES('GP Invoices SCENARIOS'[CY]) = "CY5",'$ Spend Cumulative'[$ Spend Cumulative Value],0)))))

This is the formula that I tried to use to make the adj amount cumulative:

Adj Amount Cumulative running total in Contract Year =

CALCULATE(

    [Adj Amount Cumulative],

    FILTER(

        ALLSELECTED('Dates'[Contract Year]),

        ISONORAFTER('Dates'[Contract Year], MAX('Dates'[Contract Year]), DESC)

    )

)

The $ Spend Cumulative comes from the slicer and has this formula:

$ Spend Cumulative Value = SELECTEDVALUE('$ Spend Cumulative'[Additional Amount Spent Cumulative])

This is what my table currently looks like:

                                                  Base spend after

              Base Spend        Adjusted Spend    Adjusted Amount

CY1         $200                   $200                                       $0

CY2        $500                    $400                                     -$100

CY3        $600                    $500                                      -$100

CY4        $800                    $700                                      -$100

Total      $2,100                 $1,800                                -$100                        

Here is what I would like the table to display.

                                                  Base spend after

              Base Spend        Adjusted Spend    Adjusted Amount

CY1         $200                   $200                                       $0

CY2        $500                    $400                                     -$100

CY3        $600                    $400                                      -$200

CY4        $800                    $500                                      -$300

Total      $2,100                   $1,500                                 -$600   

[v-shex-msft · Accepted solution]

Hi @enedella ,

You can try to use following measure formula if it works for your scenario:

Measure =
VAR _select =
    SELECTEDVALUE ( 'GP Invoices SCENARIOS'[CY] )
VAR _date =
    CALCULATETABLE (
        MAX ( Dates[Date] ),
        FILTER ( ALL ( Dates ), [Contract Year] = _select )
    )
RETURN
    CALCULATE (
        SUM ( Table[Based Spend] ) - SUM ( Table[Adjusted Spend] ),
        FILTER ( ALLSELECTED ( Table ), Table[Date] <= _date )
    )

If above not help, please share some sample data for test.

Regards,
Xiaoxin Sheng

[enedella]

Thank you, I will try this. Will you please explain the measure? @v-shex-msft 

[v-shex-msft]

Hi @enedella ,

Sure, you can take a look at following formula logic if it helps.

Logic:

1. Use selectedvalue function to get selection from slicer and stored to variable.
2. Use selected CY value to find out correspond date range, then extract max date from that range and cache to variable.
3. Do cumulative calculation on date range (less than or equal to max date which I calculated above).

Regards,

Xiaoxin Sheng