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
Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Microsoft Ignite 768x460.png

Find your focus

Explore the latest tools,training sessions,technical expertise, networking and more.

Power BI Womens Summit 2021 768 x460.jpg

Interviews, learning sessions, allies, and more!

#PowerBIWomenSummit

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!