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)
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....
Go to Solution.
Based on your data here, you can try out this formula.
VAR currentYM = [yearmonth]
VAR maxActualYM =
MAX ( [yearmonth] ),
FILTER ( 'Table1', 'Table1'[type] = "actual" )
VAR maxYMRevenue =
SUM ( 'Table1'[Revenue] ),
FILTER ( 'Table1', 'Table1'[yearmonth] = maxActualYM )
VAR accumulateForecast =
SUM ( Table1[Revenue] ),
'Table1'[type] = "forecast"
&& 'Table1'[yearmonth] <= currentYM
IF ( [type] = "actual", [Revenue], maxYMRevenue + accumulateForecast )
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
Learn how to create your own user groups today!
Click here to read more about the November 2021 Updates!
Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.