Forum Discussion

ArchStanton's avatar
ArchStanton
Icon for Power Participant rankPower Participant
3 months ago
Solved

Measure 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 ...
Need Help
Show and Tell
  • oussamahaimoud's avatar
    oussamahaimoud
    3 months ago

    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.

     

Kedar_Pande's avatar
Kedar_Pande
Icon for Super User rankSuper User
3 months ago

  

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" use FORMAT(TODAY(), "Mmm") instead of MONTH(TODAY()).

oussamahaimoud