Return Max Month -1 Usages when filtered

[JamesBurke]

Hi All , 

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

Max Month Usages -1 = 
Var MaxMonthValue = 
CALCULATE(
    MAX('Date'[Year Month Number New]) -1 ,
    FILTER('Date',[UsagesCount] >= 1),
    ALL('Date'))
    RETURN 
CALCULATE(
    [Total Kwh],'Date'[Year Month Number New] = 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.

Max Usages -1 Example Data.pbix

Thanks , James. 

[samratpbi]

Hi,

if you want to select a date and based on that you want to show max month's value and previous months value, then I think ideally the month filter should be single select, as selecting multiple months and showing max month and previous month might be confusing I feel.
Now if your month is single select, then you can simply use below measures for Max month and Previous months value.
for Max month:

Total Kwh = SUM('Usages'[UsagesKWH]) ( you already created this)

for previous month:

Total Kwh Prev Month =

CALCULATE(

    [Total Kwh],

    PREVIOUSMONTH('Date'[Date])

)

Also make the month slicer as single select.
If this resolves your problem, then please mark this as solution, thanks!

[_AAndrade · Accepted solution]

Hi @JamesBurke,

Please check if this is what you're looking for:

I'm using this DAX formula:

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

[JamesBurke]

Hi @_AAndrade 

You are amazing , Thankyou so much !! 

[_AAndrade]

You're welcome.