Create Running totals with What-if measures.

[Anonymous]

I am trying to prompt two variables from the user to illustrate the trend over 12 months.

I have used what-if function here to prompt two values from the user.

1. Parameter[Parameter Value]

2. Parameter 2[Parameter Value 2]

While running a calculated field based on multiplying the two Parameters, I have got:

Outlook Margin = IF('Sales'[Actual margin] == 0, Parameter[Parameter Value]*'Parameter 2'[Parameter Value 2], 'Sales'[Actual margin])

On creating a running total based on this,

I am running into my issue.

Running total does not add the replaced values by the parameter. 

 How do I change my Outlook Margin RT to include values from August?

[Anonymous]

Here is the linkk to download my pbix file https://app.powerbi.com/links/74mArxsJN5?ctid=032bca7f-8b89-4d88-a182-558d8ce2e16f&pbi_source=linkSh...

[amitchandak · Accepted solution]

@Anonymous ,  Try a measure like this, prefer a date table

Cumm = CALCULATE(Sumx(values('Date'[Month Year]),[Outlook Margin]) ,filter(allselected('Date'),'Date'[date] <=max('Date'[date])))

[Anonymous]

Thanks for the replay @amitchandak. I wanted to know how I can include a month condition element into Outlook margin when I want a sepecific month along with 'Sales'[Actual margin] == 0?

Outlook Margin = IF('Sales'[Actual margin] == 0, Parameter[Parameter Value]*'Parameter 2'[Parameter Value 2], 'Sales'[Actual margin])

I tried this, however the second condition does not seem to work, 

Outlook Margin Oct = IF(AND('Sales'[Actual margin] == 0,DATE(2022,10,31)), 'Parameter Margin'[Parameter Value]*'Parameter Volume'[Parameter Value 2], 'Sales'[Actual margin])

[Anonymous]

Hi @Anonymous,

You can't directly use date value as condition in if statement, I'd like to suggest you add the date field to compare with this values:

Outlook Margin Oct =
IF (
    AND ( 'Sales'[Actual margin] = 0, 'Sales'[Date] = DATE ( 2022, 10, 31 ) ),
    'Parameter Margin'[Parameter Value] * 'Parameter Volume'[Parameter Value 2],
    'Sales'[Actual margin]
)

Regards,

Xiaoxin Sheng