Dax measure to get sum of highest volume in 2 consecutive months

[o59393]

Hi all

I have a dax measure that should return me the sum of 2 highest volume in consecutive months.

The table as example is the following:

As seen above, the dax should first the max demand (March in this case) and its predecessor (February). 

First dax I have is the one for the max demand:

VAR _A =

MAXX(
    VALUES(Query1[Period[Month 445]]]),
    Query1[Rolling with % produced by plant]
    )
    

It returns correctly:

Then I calculate the maximum month (which should be March in this example):

Validation Max Month = 


VAR _B =

CALCULATE (
        MAX ( Query1[Period[Month 445]]] ), 
        FILTER ('Query1', [Rolling with % produced by plant] = MAXX(VALUES(Query1[Period[Month 445]]]),Query1[Rolling with % produced by plant])))
    
        return _B

Is returning December which is incorrect (THIS IS THE DAX THAT NEEDS TO BE FIXED):

Then I calculate the predecessor month:

VAR _b =

CALCULATE (
        MAX ( Query1[Period[Month 445]]] ), 
        FILTER ('Query1', [Rolling with % produced by plant] = MAXX(VALUES(Query1[Period[Month 445]]]),Query1[Rolling with % produced by plant])))

VAR _D =
    DATE ( YEAR ( _B ), MONTH ( _B )-1, DAY ( _B ) )

RETURN _D

In this case returns November, which is incorrect (should be february)

The following dax is the total year demand:

Finally I calculate the 2 consecutive months:

2 Month Max demand = 

Var _a = 

MAXX(
    VALUES(Query1[Period[Month 445]]]),
    Query1[Rolling with % produced by plant]
    )

VAR _b =

CALCULATE (
        MAX ( Query1[Period[Month 445]]] ), 
        FILTER ('Query1', [Rolling with % produced by plant] = MAXX(VALUES(Query1[Period[Month 445]]]),Query1[Rolling with % produced by plant])))

Var _c = [Rolling with % produced by plant]

VAR _D =
    DATE ( YEAR ( _B ), MONTH ( _B )-1, DAY ( _B ) )

RETURN
_a + CALCULATE ( [Rolling with % produced by plant] , Query1[Period[Month 445]]] = _D )

As seen the result it return is not correct, the dax above is summing March (3.6MM) and November (2.4)

It should be march (3.6) and feb (2.6).

How can I solve it?

[Jihwan_Kim]

Hi,

I am not sure if I understood your question correctly, but please chech the below picture and the attached pbix file.

Expected result measure: =
VAR _topvalue =
    CALCULATE (
        SUM ( Data[KO Volume] ),
        KEEPFILTERS (
            WINDOW (
                1,
                ABS,
                1,
                ABS,
                ALL ( Data[Month] ),
                ORDERBY ( CALCULATE ( SUM ( Data[KO Volume] ) ), DESC )
            )
        )
    )
VAR _prevmonthend =
    EOMONTH (
        FILTER (
            ALL ( Data[Month] ),
            CALCULATE ( SUM ( Data[KO Volume] ) ) = _topvalue
        ),
        -1
    )
VAR _prevmonthvalue =
    CALCULATE (
        SUM ( Data[KO Volume] ),
        EOMONTH ( Data[Month], 0 ) = _prevmonthend
    )
RETURN
    _topvalue + _prevmonthvalue

[o59393]

Hi @Jihwan_Kim 

Sorry if I wasn't clear. Let me put it easier.

How can I return in a dax measure the month whose got the highest volume in any month?

In this case the highest volume is march, therefore the dax should return me March:

I already have calculated the maximum volume for march:

How could I get the march month with dax?

Thanks.

[Jihwan_Kim · Accepted solution]

Hi,

Thank you for your message.

Please check the below picture and the attached pbix file.

The below measure is one of many ways to achieve it.

[o59393]

You solved my problem @Jihwan_Kim 

Thank you!