Forum Discussion
Power ParticipantMeasure needs to be fine-tuned
I have the following measure that was created specifically for a tooltip so that the user could see the MAX & MIN month which works just fine in my Table visual
MinMaxMth =
VAR AllMonths =
CALCULATETABLE (
ADDCOLUMNS ( VALUES ( Date2[Mth] ), "Value", [Primary Topics] ),
ALLSELECTED ( 'Date2' )
)
VAR ValidMonths =
FILTER ( AllMonths, NOT ISBLANK ( [Value] ) && [Value] > 0 ) -- Single MIN month
VAR MinMonth =
MAXX ( TOPN ( 1, ValidMonths, [Value], ASC, 'Date2'[Mth], ASC ), Date2[Mth] ) -- Single MAX month
VAR MaxMonth =
MAXX ( TOPN ( 1, ValidMonths, [Value], DESC, 'Date2'[Mth], ASC ), Date2[Mth] )
VAR CurrentMonth =
SELECTEDVALUE ( Date2[Mth] )
RETURN
SWITCH (
TRUE (),
CurrentMonth = MinMonth, "MIN",
CurrentMonth = MaxMonth, "MAX",
BLANK ()
)
The measure falls down when filters are applied to the current year because if we're only a few days into a new month then the total for the current month will always = MIN:
Is there a way of tweaking the DAX code above to exclude the current month so MIN will be May instead of June?
Hi ArchStanton,
Hope you're doing well!
you just need to exclude the current calendar month from the ValidMonths variable before the MIN/MAX calculation runs.
Add a TODAY() reference to capture the current month, then filter it out from ValidMonths:
MinMaxMth =
VAR AllMonths =
CALCULATETABLE (
ADDCOLUMNS ( VALUES ( Date2[Mth] ), "Value", [Primary Topics] ),
ALLSELECTED ( 'Date2' )
)
VAR CurrentCalendarMonth =
FORMAT ( TODAY (), "MMM" )
VAR ValidMonths =
FILTER (
AllMonths,
NOT ISBLANK ( [Value] )
&& [Value] > 0
&& Date2[Mth] <> CurrentCalendarMonth
)
VAR MinMonth =
MAXX ( TOPN ( 1, ValidMonths, [Value], ASC, 'Date2'[Mth], ASC ), Date2[Mth] )
VAR MaxMonth =
MAXX ( TOPN ( 1, ValidMonths, [Value], DESC, 'Date2'[Mth], ASC ), Date2[Mth] )
VAR CurrentMonth =
SELECTEDVALUE ( Date2[Mth] )
RETURN
SWITCH (
TRUE (),
CurrentMonth = MinMonth, "MIN",
CurrentMonth = MaxMonth, "MAX",
BLANK ()
)
The key addition is CurrentCalendarMonth using FORMAT(TODAY(), "MMM"), which needs to match exactly the format your Mth column uses, if your column stores full month names like "June" rather than "Jun", just change the format string to "MMMM" accordingly.
3 Replies
- Kedar_Pande
Super User
Exclude the current/incomplete month from ValidMonths before ranking.
VAR CurrentMth = MONTH(TODAY())
VAR ValidMonths =
FILTER(
AllMonths,
NOT ISBLANK([Value]) && [Value] > 0
&& Date2[Mth] <> CurrentMth
)This drops the running month so MIN lands on May instead of the partial June. Adjust the
Date2[Mth] <> CurrentMth comparison to match your column type, if Mth is a name like "Jun" useFORMAT(TODAY(), "Mmm") instead of MONTH(TODAY()). - oussamahaimoud
Memorable Member
Hi ArchStanton,
Hope you're doing well!
you just need to exclude the current calendar month from the ValidMonths variable before the MIN/MAX calculation runs.
Add a TODAY() reference to capture the current month, then filter it out from ValidMonths:
MinMaxMth =
VAR AllMonths =
CALCULATETABLE (
ADDCOLUMNS ( VALUES ( Date2[Mth] ), "Value", [Primary Topics] ),
ALLSELECTED ( 'Date2' )
)
VAR CurrentCalendarMonth =
FORMAT ( TODAY (), "MMM" )
VAR ValidMonths =
FILTER (
AllMonths,
NOT ISBLANK ( [Value] )
&& [Value] > 0
&& Date2[Mth] <> CurrentCalendarMonth
)
VAR MinMonth =
MAXX ( TOPN ( 1, ValidMonths, [Value], ASC, 'Date2'[Mth], ASC ), Date2[Mth] )
VAR MaxMonth =
MAXX ( TOPN ( 1, ValidMonths, [Value], DESC, 'Date2'[Mth], ASC ), Date2[Mth] )
VAR CurrentMonth =
SELECTEDVALUE ( Date2[Mth] )
RETURN
SWITCH (
TRUE (),
CurrentMonth = MinMonth, "MIN",
CurrentMonth = MaxMonth, "MAX",
BLANK ()
)
The key addition is CurrentCalendarMonth using FORMAT(TODAY(), "MMM"), which needs to match exactly the format your Mth column uses, if your column stores full month names like "June" rather than "Jun", just change the format string to "MMMM" accordingly.
ArchStantonThanks, the works perfectly, its always obvious when you see the solution
