Max Month usages

[JamesBurke]

Hi All , 

Max Month Usages = 
Var MaxMonthValue = 
CALCULATE(
    MAX('Date'[Year Month Number]) ,
    FILTER('Date',[UsagesCount] >= 1),
    ALL('Date'))
    RETURN 
CALCULATE(
    [Total Kwh],'Date'[Year Month Number] = MaxMonthValue)

Max Month Usages -1 = 
Var MaxMonthValue = 
CALCULATE(
    MAX('Date'[Year Month Number]) -1 ,
    FILTER('Date',[UsagesCount] >= 1),
    ALL('Date'))
    RETURN 
CALCULATE(
    [Total Kwh],'Date'[Year Month Number] = MaxMonthValue)

UsagesCount = CALCULATE(COUNTROWS('Emporia Device Usage'))

 

These measures return the highest Months value ( the Max of the Months where there are usages ) by using the usages count Measure which works until i click or filter down onto a specfic month, The Max Month Usages -1 Then returns a blank value However Max Month usages works. 

Desired outcome : 

Have Max Month -1 return a value when filtered so the users can look at specfic seleted Months.

Thanks , James. 

[v-huijiey-msft]

Hi @JamesBurke ,

The core of the issue seems to be related to how the filter context is applied in your DAX formula for "Max Month Usages -1".

When you filter down to a specific month, the function's filter context is changed, which affects how the function computes the maximum value of 'Date'[Year Month Number].

Please try:

Max Month Usages -1 = 
VAR CurrentMaxMonthValue = 
    CALCULATE(
        MAX('Date'[Year Month Number]),
        ALLSELECTED('Date')
    )
VAR MaxMonthValue = 
    CALCULATE(
        MAX('Date'[Year Month Number]) - 1,
        FILTER(
            ALL('Date'),
            'Date'[Year Month Number] < CurrentMaxMonthValue
            && [UsagesCount] >= 1
        )
    )
RETURN 
    CALCULATE(
        [Total Kwh],
        'Date'[Year Month Number] = MaxMonthValue
    )

I would be grateful if you could provide me with sample data for testing, please remove any sensitive data in advance.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[JamesBurke]

Hi @v-huijiey-msft , 

Thanks for this ! unfortunately the issue still remains. 

Max Usages -1 Example Data.pbix

Attached is some example data  without the sensetive data, It uses my old measurments as well as the one you suggested. 

Thanks, James