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.

     

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    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.

     

    • AlexisOlson's avatar
      AlexisOlson
      Super User
      4 years ago

      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.

       

       

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    4 years ago

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

     

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