Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Hello,
I am trying to calculate the "Forecast Value" based on the "Color" below.
What would be the DAX formula for this?
Solved! Go to Solution.
I doubt you can do the logic and cumulative sum in one dax. You can create a logic column then get the forecast value in a measure.
Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.
I doubt you can do the logic and cumulative sum in one dax. You can create a logic column then get the forecast value in a measure.
Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.
A calculated column is definitely recommended here but you can do without if you need to (e.g. if the color values are dynamic).
Forcast (AO) =
VAR MaxDate = MAX ( Table1[YearMonth] )
VAR ColorScores =
ADDCOLUMNS (
SUMMARIZE (
FILTER ( ALL ( Table1 ), Table1[YearMonth] <= MaxDate ),
Table1[YearMonth],
Table1[Color]
),
"@Init", CALCULATE ( SUM ( Table1[Initial Value] ) ),
"@Incr", SWITCH ( Table1[Color], "Red", 1, "Green", 2, "Blue", 3 )
)
RETURN
SUMX ( ColorScores, MAX ( [@Init], [@Incr] ) )
See attached.
If you have one color per month, then you can calculate the final forecasted value over a selected period like this:
Forcast =
VAR ColorScores =
ADDCOLUMNS (
SUMMARIZE ( Table1, Table1[Month], Table1[Color] ),
"@Incr", SWITCH ( Table1[Color], "Red", 1, "Blue", 2, "Green", 3 )
)
RETURN
[InitialValue] + SUMX ( ColorScores, [@Incr] )
I don't think the above code works...
Am I missing something?
The forecast total works as I intended. You have to do a bit more work to get the months to work like a cumulative total.
Something more like this:
Forcast =
VAR MaxDate = MAX ( Table1[Date] )
VAR InitialValue = CALCULATE ( SUM ( Table1[InitialValue] ), Table1[Date] <= MaxDate )
VAR ColorScores =
ADDCOLUMNS (
SUMMARIZE (
FILTER ( Table1, Table1[Date] <= MaxDate ),
Table1[Month],
Table1[Color]
),
"@Incr", SWITCH ( Table1[Color], "Red", 1, "Blue", 2, "Green", 3 )
)
RETURN
InitialValue + SUMX ( ColorScores, [@Incr] )
I am getting the same result
Probably because you have Year and Month context in your visual rather than just Date.
You can wipe out all of the filter context with ALL.
Forcast =
VAR MaxDate = MAX ( Table1[Date] )
VAR InitialValue =
CALCULATE (
SUM ( Table1[InitialValue] ),
FILTER ( ALL ( Table1 ), Table1[Date] <= MaxDate )
)
VAR ColorScores =
ADDCOLUMNS (
SUMMARIZE (
FILTER ( ALL ( Table1 ), Table1[Date] <= MaxDate ),
Table1[Month],
Table1[Color]
),
"@Incr", SWITCH ( Table1[Color], "Red", 1, "Blue", 2, "Green", 3 )
)
RETURN
InitialValue + SUMX ( ColorScores, [@Incr] )
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
10 | |
9 | |
8 | |
6 | |
5 |
User | Count |
---|---|
17 | |
14 | |
10 | |
9 | |
6 |