Forum Discussion

kangx322's avatar
kangx322
Frequent Visitor
4 years ago
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?   
  • Anonymous's avatar
    Anonymous
    4 years ago

    kangx322 

     

    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.

     

kangx322's avatar
kangx322
Frequent Visitor
4 years ago

I don't think the above code works... 
Am I missing something? 

 

AlexisOlson's avatar
AlexisOlson
Icon for Super User rankSuper User
4 years ago

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] )
  • kangx322's avatar
    kangx322
    Frequent Visitor
    4 years ago

    I am getting the same result

     

    • AlexisOlson's avatar
      AlexisOlson
      Icon for Super User rankSuper User
      4 years ago

      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] )