Forum Discussion

RAHEEL's avatar
RAHEEL
Helper I
8 years ago
Solved

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....

 

yearmonthtypeRevenueDesired Output Column
201701actual1010
201702actual3030
201703actual6060
201704actual100100
201705actual150150
201706actual210210
201707actual280280
201708actual360360
201709actual450450
201710forecast10460
201711forecast20480
201712forecast30510
201801forecast40550
201802forecast50600

 

 

Regards,

Raheel Farooq

Need Help
Show and Tell
  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    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 )

    Calculate_Column_conditionally

     

    Best Regard,

    Dale

2 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    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 )

    Calculate_Column_conditionally

     

    Best Regard,

    Dale

    • RAHEEL's avatar
      RAHEEL
      Helper I
      8 years ago

      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