cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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

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

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

Proud to be a Super User!

4 REPLIES 4
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!
Super User

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

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

Proud to be a Super User!

Helper II

You are amazing , Thankyou so much !!

Super User

You're welcome.

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

Proud to be a Super User!