Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
kangx322
Frequent Visitor

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? 

kangx322_0-1634743999983.png

 

1 ACCEPTED SOLUTION
V-pazhen-msft
Community Support
Community Support

@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])))
Vpazhenmsft_1-1635401927726.png

 

 

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

7 REPLIES 7
V-pazhen-msft
Community Support
Community Support

@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])))
Vpazhenmsft_1-1635401927726.png

 

 

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.

AlexisOlson_0-1635432245575.png

 

 

AlexisOlson
Super User
Super User

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? 

kangx322_0-1634759504949.png

 

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

kangx322_0-1634764266000.png

 

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

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors