Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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....
yearmonth | type | Revenue | Desired Output Column |
201701 | actual | 10 | 10 |
201702 | actual | 30 | 30 |
201703 | actual | 60 | 60 |
201704 | actual | 100 | 100 |
201705 | actual | 150 | 150 |
201706 | actual | 210 | 210 |
201707 | actual | 280 | 280 |
201708 | actual | 360 | 360 |
201709 | actual | 450 | 450 |
201710 | forecast | 10 | 460 |
201711 | forecast | 20 | 480 |
201712 | forecast | 30 | 510 |
201801 | forecast | 40 | 550 |
201802 | forecast | 50 | 600 |
Regards,
Raheel Farooq
Solved! Go to Solution.
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 )
Best Regard,
Dale
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 )
Best Regard,
Dale
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |