Forum Discussion
Solved
DAX calculation with incremental value
Hello, I am trying to calculate the "Forecast Value" based on the "Color" below. What would be the DAX formula for this?
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.
forecastlogic column=var logic = SWITCH([Color],"Red",1,"Green",2,"Blue",3)Return IF([Initial Value]=100,100,logic)Forcast value = CALCULATE(SUM('Table'[forecastlogic]),FILTER(ALL('Table'),[YearMonth]<=MAX([YearMonth])))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.
forecastlogic column=
var logic = SWITCH([Color],"Red",1,"Green",2,"Blue",3)
Return IF([Initial Value]=100,100,logic)
Forcast value = CALCULATE(SUM('Table'[forecastlogic]),FILTER(ALL('Table'),[YearMonth]<=MAX([YearMonth])))
Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.
AlexisOlson
Super User
Super UserA 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.