Calculate Column conditionally

[RAHEEL]

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

[v-jiascu-msft · Accepted solution]

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

[RAHEEL]

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