Compare the two months Selected and display the values.

[Samarth-Borade]

I have selected Two months : Jan and Feb , Now i have a measure "Max Trip"

Max Trip =

    VAR MaxValue = MAXX({[Monday], [Tuesday], [Wednesday], [Thursday], [Friday], [Saturday], [Sunday]}, [Value])

    RETURN

        SWITCH(

            MaxValue,

            [Monday], "Monday",

            [Tuesday], "Tuesday",

            [Wednesday], "Wednesday",

            [Thursday], "Thursday",

            [Friday], "Friday",

            [Saturday], "Saturday",

            [Sunday], "Sunday"

        )

Now i hava measure trip_prev which shows the Max trip of least month selected:

trip_prev =

    VAR MinMonth = MIN(MV_QKM_DATA[Trip Date].[MonthNo])

    RETURN CALCULATE([Max Trip], FILTER(MV_QKM_DATA, MV_QKM_DATA[Trip Date].[MonthNo] = MinMonth))

Now i hava measure trip_prev which shows the Max trip of max month selected:

trip_curr = VAR MaxMonth = MAX(MV_QKM_DATA[Trip Date].[MonthNo])

    RETURN CALCULATE([Max Trip], FILTER(MV_QKM_DATA, MV_QKM_DATA[Trip Date].[MonthNo] = MaxMonth))

Now i want to display those Route Path when the Max Trip is same for both selected months i.e trip_curr==trip_prev .

Same Route Paths Prev and Curr =

IF([trip_curr]==[trip_prev],VALUES(MV_QKM_DATA[Route Path]),BLANK())

But here it is aslo printing the Route Paths where only One month is there, i want to show the Route Paths where both the month entries are there and they are same . Help me 

[v-shex-msft · Accepted solution]

Hi @Samarth-Borade,

You can try to add a variable to compare the current and previous month and use it as condition in your formula to exclude same month scenarios:

Same Route =
VAR MinDate =
    MIN ( MV_QKM_DATA[Trip Date] )
VAR MaxDate =
    MAX ( MV_QKM_DATA[Trip Date] )
VAR _minTrip =
    CALCULATE (
        [Max Trip],
        FILTER (
            MV_QKM_DATA,
            YEAR ( MV_QKM_DATA[Trip Date] ) = YEAR ( MinDate )
                && MONTH ( MV_QKM_DATA[Trip Date] ) = MONTH ( MinDate )
        )
    )
VAR _maxTrip =
    CALCULATE (
        [Max Trip],
        FILTER (
            MV_QKM_DATA,
            YEAR ( MV_QKM_DATA[Trip Date] ) = YEAR ( MaxDate )
                && MONTH ( MV_QKM_DATA[Trip Date] ) = MONTH ( MaxDate )
        )
    )
VAR flag =
    IF (
        YEAR ( MinDate ) * 100 + MONTH ( MinDate )
            = YEAR ( MaxDate ) * 100 + MONTH ( MaxDate ),
        "Y",
        "N"
    )
RETURN
    IF (
        flag = "N"
            && _minTrip = _maxTrip,
        SELECTEDVALUE ( MV_QKM_DATA[Route Path] )
    )

Regards,

Xiaoxin Sheng