Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Samarth-Borade
Frequent Visitor

Compare the two months Selected and display the values.

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 
1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

1 REPLY 1
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.