cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RAHEEL
Helper I
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....

 

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

 

 

Regards,

Raheel Farooq

1 ACCEPTED SOLUTION
v-jiascu-msft
Microsoft
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 )

Calculate_Column_conditionally

 

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.

View solution in original post

2 REPLIES 2
v-jiascu-msft
Microsoft
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 )

Calculate_Column_conditionally

 

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.

View solution in original post

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

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!