Forum Discussion

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

Min Max Tooltip & future months

I want to be able to use conditional formatting (cell colour) to show the Max and Min month in a tooltip.   The problem I'm having is that current measure doesn't work because its showing Feb & Mar...
Need Help
Show and Tell
DanieleUgoCopp's avatar
DanieleUgoCopp
Icon for Super User rankSuper User
6 months ago

Hello,

I think you can fix it reducing the range that is considering the conditional formatting, you can change your dax to this:

MinMax Tooltip =
VAR LastMonthWithData =
    CALCULATE(
        MAX('Calendar'[Month]),
        FILTER(
            ALL('Calendar'),
            NOT ISBLANK([#])
        )
    )

VAR ValidMonths =
    FILTER(
        ALLSELECTED('Calendar'),
        'Calendar'[Month] <= LastMonthWithData
            && NOT ISBLANK([#])
    )

VAR MinValue =
    MINX(ValidMonths, [#])

VAR MaxValue =
    MAXX(ValidMonths, [#])

VAR CurrentValue = [#]

RETURN
    SWITCH(
        TRUE(),
        CurrentValue = MinValue, "MIN",
        CurrentValue = MaxValue, "MAX",
        BLANK()
    )


Thank you,
Daniele

  • ArchStanton's avatar
    ArchStanton
    Icon for Power Participant rankPower Participant
    6 months ago

    Thanks Daniele, I tried your code and it removed the 2 future months but there is no Max or Min:

     

     

    MinMax Tooltip2 = 
VAR LastMonthWithData = 
    CALCULATE(
        MAX(Date2[Mth]),
        FILTER(
            ALL(Date2),
            NOT ISBLANK([#])
        )
    )
VAR ValidMonths = 
    FILTER(
        ALLSELECTED(Date2),
        Date2[Mth] <= LastMonthWithData
            && NOT ISBLANK([#])
    )
VAR MinValue =
    MINX(ValidMonths,[#])

VAR MaxValue = 
    MAXX(ValidMonths, [#])

VAR CurrentValue = [#]

RETURN
    SWITCH(
        TRUE(),
        CurrentValue = MinValue, "Min",
        CurrentValue = MaxValue, "Max",
        BLANK()
    )

     

    One other thing, when I use DAX Formatter on the Code I get the following error in the MinValue: