Forum Discussion
Cumulative What if Parameter
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
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
3 Replies
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 ShengThank you, I will try this. Will you please explain the measure? Anonymous
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
