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

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