Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
JamesBurke
Helper III
Helper III

Max Month usages

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'))

 

 JamesBurke_0-1712830117806.png

 

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. 

 

2 REPLIES 2
v-huijiey-msft
Community Support
Community Support

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!

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.