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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
JamesBurke
Helper II
Helper II

Return Max Month -1 Usages when filtered

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. 

1 ACCEPTED SOLUTION
_AAndrade
Super User
Super User

Hi @JamesBurke,

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

_AAndrade_0-1713269089736.png

 

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








Did I answer your question? Mark my post as a solution! Kudos are welcome.

Proud to be a Super User!




View solution in original post

4 REPLIES 4
samratpbi
Solution Supplier
Solution Supplier

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
Super User
Super User

Hi @JamesBurke,

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

_AAndrade_0-1713269089736.png

 

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








Did I answer your question? Mark my post as a solution! Kudos are welcome.

Proud to be a Super User!




Hi @_AAndrade 

 

You are amazing , Thankyou so much !! 

You're welcome.





Did I answer your question? Mark my post as a solution! Kudos are welcome.

Proud to be a Super User!




Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.