How to Sum two values into one Row

[Stuznet]

Hi guys,

I'm struggling to sum two measures into one Row.

I have two Measure

Measure 1

Var = CALCULATE(SUM([Value]),
        FILTER(
            ALLSELECTED(Actual),
            Actual[Month_Num] <= MAX(Actual[Month_Num])))

Measure 2

Forecast_Var = CALCULATE(SUM(Forecast[Value]),
                FILTER(Forecast, Forecast[Month_Num] >= MONTH(TODAY())))

I did it in Excel and this is the result I wanted to appear in power bi

May + June = 

4228 + 315,489 = 319,717

June + July

319,717 + 132,467,489 = 132,782,978

and so on ......

Here is the dummy file

I really appreciate your help

[Anonymous · Accepted solution]

Try this measure

Result = 
VAR LastMonthInActuals =
    MAX ( Actual[Month_Num] )
VAR MonthInContext =
    MAX ( Month_Tbl[Month_Num] )
VAR CumulativeTotalOfActual =
    SUMX ( ALLSELECTED ( Actual ), Actual[Value] )
VAR CumulativeMonthlyActuals =
    CALCULATE (
        SUM ( [Value] ),
        FILTER ( ALLSELECTED ( Actual ), Actual[Month_Num] <= LastMonthInActuals )
    )
VAR CumulativeForecast =
    SUMX (
        FILTER (
            ALL ( Forecast ),
            Forecast[Month_Num] > [LastMonthInActuals]
                && Forecast[Month_Num] <= MonthInContext
        ),
        Forecast[Value]
    )
RETURN
    IF (
        MonthInContext <= LastMonthInActuals,
        CumulativeMonthlyActuals,
        CumulativeForecast + CumulativeTotalOfActual
    )

[v-juanli-msft]

Hi @Stuznet 

This will need to create a new table with some measures and columns.

Where do you apply filters?

Which is static and which is dynamic?

Because my solution may be static for some part.

Best Regards
Maggie

[Stuznet]

Hi @v-juanli-msft 

I'm unsure what you mean but this is the third measure I created but it doesn't sum May + June, June + July. 

Total = SWITCH(TRUE(),
            MAX(Category[Category]) = "Cat1",
                [Forecast_Var] + [Var])

[Cmcmahan]

It took me a bit, but I think what you want is a cumulative sum? Your first measure is a cumulative sum, but then you get weird in the forecasting bit. 

If you could explain how you calculate the Forecast[Value] in the first place, it would be very helpful. In your expected outcome, it looks like Measure2 is already set up as a reverse cumulative sum (essentially summing everything that hasn't happened yet), and then you're looking to add the previous month's reverse sum again in the table?  

If this is truly the case, you may be able to use this measure to get the forecasted month values:

Forecast_Var_plus_prev_month = CALCULATE(SUM(Forecast[Value]),
                FILTER(Forecast, Forecast[Month_Num] >= MONTH(TODAY())-1))

Either way, the math that I'm seeing seems weird, and I'd love to know what's up.

[Stuznet]

@Cmcmahan 

I apologize I couldn't better explain to you. The function you provided isn't what I'm looking for.

I just sum the forecast value, 

CALCULATE(SUM(Forecast[Value]),

with this formula, I want it to start summing from June and not May.

FILTER(Forecast, Forecast[Month_Num] >= MONTH(TODAY())))

I did this in Excel, I just want to illustrate.

I hope that make sense

[Cmcmahan]

Ah, then you probably want an IF or SWITCH statement:

Assuming you have a list of months already pre-generated, here's some pseudocode that should get you closer:

CalcColumn = 
VAR PrevValue = CALCULATE(ThisTable[Value], FILTER(ALL(ThisTable), ThisTable[Month] < EARLIER(ThisTable[Month]) && ThisTable[Month] = MAX(ThisTable[Month])) )
RETURN
IF( ThisTable[Month]<MONTH(NOW()), [Measure1], [Measure2] + PrevValue)

This should get you at least closer.  Getting the previous value in a row via DAX is tricky at best.  

[Stuznet]

@Cmcmahan 

I've tried your formula, but the result still the same. It didn't sum....here is the dummy file. 

CalcColumn2 = 
VAR PrevValue = CALCULATE(Forecast[Forecast_Var], 
                    FILTER(ALL(Forecast), 
                    Forecast[Month_Num] < EARLIER(Forecast[Month_Num]) && 
                    Forecast[Month_Num] = MAX(Forecast[Month_Num])))
                    
RETURN
    IF(Forecast[Month_Num] < MONTH(NOW()), [Var], [Forecast_Var] + PrevValue)

Am doing something wrong? 

[Ashish_Mathur]

Hi,

Is this the result you are expecting?  You may download my PBI file from here.

Hope this helps.

[Stuznet]

@Ashish_Mathur 

Unfortunately, it is not the result I'm looking for.  I appreciated your help

[Anonymous · Accepted solution]

Try this measure

Result = 
VAR LastMonthInActuals =
    MAX ( Actual[Month_Num] )
VAR MonthInContext =
    MAX ( Month_Tbl[Month_Num] )
VAR CumulativeTotalOfActual =
    SUMX ( ALLSELECTED ( Actual ), Actual[Value] )
VAR CumulativeMonthlyActuals =
    CALCULATE (
        SUM ( [Value] ),
        FILTER ( ALLSELECTED ( Actual ), Actual[Month_Num] <= LastMonthInActuals )
    )
VAR CumulativeForecast =
    SUMX (
        FILTER (
            ALL ( Forecast ),
            Forecast[Month_Num] > [LastMonthInActuals]
                && Forecast[Month_Num] <= MonthInContext
        ),
        Forecast[Value]
    )
RETURN
    IF (
        MonthInContext <= LastMonthInActuals,
        CumulativeMonthlyActuals,
        CumulativeForecast + CumulativeTotalOfActual
    )

[Stuznet · Accepted solution]

@Anonymous 

Thank you so much for the measure, the result was wrong but I modified it and it works 🙂

See measure in Red. Your original measure was Forecast[Value], I replaced with Forecast[Forecast_Var])

Forecast_Var = CALCULATE(SUM(Forecast[Value]),
                FILTER(Forecast, Forecast[Month_Num] >= MONTH(TODAY())))

This is the final measure

Measure = 
VAR LastMonthInActuals = MAX ( Actual[Month_Num] )
VAR MonthInContext = MAX ( Month_Tbl[Month_Num] )
VAR CumulativeTotalOfActual = SUMX ( ALLSELECTED ( Actual ), Actual[Value] )
VAR CumulativeMonthlyActuals = CALCULATE (SUM ( Actual[Value] ),FILTER ( ALLSELECTED ( Actual ), Actual[Month_Num] <= LastMonthInActuals ))
VAR CumulativeForecast = SUMX (FILTER (ALL ( Forecast ),Forecast[Month_Num] > LastMonthInActuals && Forecast[Month_Num] <= MonthInContext), Forecast[Forecast_Var])
RETURN
    IF (
        MonthInContext <= LastMonthInActuals,
        CumulativeMonthlyActuals,
        CumulativeForecast + CumulativeTotalOfActual)

This the result and it summing correctly.

Thank you so much y'all. 🙂

[PaulDBrown]

@Stuznet 

Do you have a calendar table?

if so, first create a measure which delivers the actual value for the previous month. Something along the lines of: 

previous month actual = CALCULATE([Actual],

FILTER(ALL(CalendarTable),

CalendarTable[Month] = MAX(CalendarTable[Month]) -1))

then create new measure summing the current month forecast with this previous month actual. Use the calendarMonth as your x-axis.

if you want the cumulative, create  the cumulative measure with this measure as you did with your forecast measure.

(apologies if it needs polishing but I’m not in front of a pc at the moment)

EDIT: here is a pic of the proposed solution