Forum Discussion

JamesBurke's avatar
JamesBurke
Helper III
2 years ago

Max Month usages

Hi All , 

 

I hav the below Measure :

 

 

 

Max Month Usages -1 save = 
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
    )

 

 


This Works well however once another Month is selected it returns a blank value rather then the max Month usages , i would like it be dynamic so when another Month is selected it shows that Months data. 

 

Above is when may is selected 

 

 

Above is when any other Month is selected. 

 

Thanks in adavance,  James. 

 

Need Help

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi JamesBurke ,

    You can try this DAX:

    Dynamic Max Month Usages -1 Save =
VAR CurrentMaxMonthValue =
    CALCULATE(
        MAX('Date'[Year Month Number]),
        ALLSELECTED('Date')
    )
VAR PreviousMaxMonthValue =
    CALCULATE(
        MAX('Date'[Year Month Number]),
        ALLSELECTED('Date'[Year Month Number]),
        'Date'[Year Month Number] < CurrentMaxMonthValue
    )
VAR Result =
    CALCULATE(
        [Total Kwh],
        FILTER(
            ALLSELECTED('Date'),
            'Date'[Year Month Number] = PreviousMaxMonthValue
        )
    )
RETURN
    Result

    Best Regards,

    Xianda Tang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.