cancel
Showing results for
Did you mean:
Helper I

Calculate Column conditionally

Hi All,

Below is my dataset.

when the type is "actual", "Revenue" column is the desired output

BUT when the type is "forecast", then desired output is the revenue of max(yearmonth) of type = actual + revenue of yearmonth of all preceding & current rows with type = 'forecast'

In the dataset below: revenue of max(yearmonth) of type = actual returns 201709 (because the maximum yearmonth with "actual" type is 201709)

e.g.

when yearmonth = 201703 then desired output = 60 (i.e. same as revenue)

when yearmonth = 201710 then desired output = 450 + 10 = 460 (i.e. revenue of maximum yearmonth with "actual" type + current row of "forecast" type, since this is the only forecast type row)

when yearmonth = 201711 then desired output = 450 + 10 + 20 = 480 (i.e. revenue of maximum yearmonth with "actual" type + preceding row of "forecast row: + current row of "forecast" type)

and so on....

 yearmonth type Revenue Desired Output Column 201701 actual 10 10 201702 actual 30 30 201703 actual 60 60 201704 actual 100 100 201705 actual 150 150 201706 actual 210 210 201707 actual 280 280 201708 actual 360 360 201709 actual 450 450 201710 forecast 10 460 201711 forecast 20 480 201712 forecast 30 510 201801 forecast 40 550 201802 forecast 50 600

Regards,

Raheel Farooq

1 ACCEPTED SOLUTION
Microsoft

Hi Raheel,

Based on your data here, you can try out this formula.

```CalculatedColumn =
VAR currentYM = [yearmonth]
VAR maxActualYM =
CALCULATE (
MAX ( [yearmonth] ),
FILTER ( 'Table1', 'Table1'[type] = "actual" )
)
VAR maxYMRevenue =
CALCULATE (
SUM ( 'Table1'[Revenue] ),
FILTER ( 'Table1', 'Table1'[yearmonth] = maxActualYM )
)
VAR accumulateForecast =
CALCULATE (
SUM ( Table1[Revenue] ),
FILTER (
'Table1',
'Table1'[type] = "forecast"
&& 'Table1'[yearmonth] <= currentYM
)
)
RETURN
IF ( [type] = "actual", [Revenue], maxYMRevenue + accumulateForecast )```

Best Regard,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Microsoft

Hi Raheel,

Based on your data here, you can try out this formula.

```CalculatedColumn =
VAR currentYM = [yearmonth]
VAR maxActualYM =
CALCULATE (
MAX ( [yearmonth] ),
FILTER ( 'Table1', 'Table1'[type] = "actual" )
)
VAR maxYMRevenue =
CALCULATE (
SUM ( 'Table1'[Revenue] ),
FILTER ( 'Table1', 'Table1'[yearmonth] = maxActualYM )
)
VAR accumulateForecast =
CALCULATE (
SUM ( Table1[Revenue] ),
FILTER (
'Table1',
'Table1'[type] = "forecast"
&& 'Table1'[yearmonth] <= currentYM
)
)
RETURN
IF ( [type] = "actual", [Revenue], maxYMRevenue + accumulateForecast )```

Best Regard,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper I

Simply beautiful Dale. Not only this solution worked smoothly but I have also learned the use of variables in calculated columns 🙂 Thankyou Dale

Regards,

Raheel Farooq

Announcements

Launching new user group features

Learn how to create your own user groups today!