MonthYear column isn't sorted correctly

[trdoan]

Hi everyone,

My apology that I can't post the pbi file here since my company policy blocks that. Here is my data:

'Data' table:

Vendor Name	TAT	Posting Date
A	145	22/05/2018
A	50	05/06/2018
A	55	12/06/2018
A	125	27/02/2018
C	23	30/04/2018
B	15	05/02/2018
C	45	27/04/2018
B	16	03/01/2018
C	19	22/03/2018
B	85	11/01/2018
C	62	01/02/2018
C	20	22/01/2018
C	23	08/03/2018
C	24	15/02/2018
C	25	14/03/2018
C	29	06/04/2018
A	63	13/04/2018
C	66	02/05/2018
C	35	30/04/2018
B	30	30/04/2018
B	31	20/04/2018
B	32	27/04/2018
B	41	12/02/2018
B	25	12/03/2018
A	25	30/03/2018
C	25	16/03/2018
B	26	26/03/2018
A	8	11/05/2018
C	6	11/06/2018
A	14	20/04/2018
A	11	23/04/2018
A	15	27/07/2018
C	18	11/07/2018
C	119	23/07/2018
B	37	25/07/2018
B	30	27/07/2018
B	31	02/07/2018
B	45	06/07/2018
A	14	29/08/2018
A	16	30/08/2018
A	65	30/08/2018
C	60	09/08/2018
C	65	13/08/2018
C	67	29/08/2018
B	62	13/08/2018
B	67	07/08/2018
B	69	29/08/2018

I have the following calculated column,

MonthYear = FORMAT([Posting Date],"mmmm yyyy")

, which is sorted by this calculated column (both in Text format):

PostingDateSorted = FORMAT([Posting Date],"yyyymm")

And the following measures (put in a Card Visual) to calculate Average TATs based on the chosen Max Month & Min Month:

Max Month TAT = 
CALCULATE (
    AVERAGE ( Data[TAT]),
        FILTER (
            ALLSELECTED ( Data ),
            Data[MonthYear]
                = MAX( ( Data[MonthYear] )
        )
))

Min Month TAT = 
CALCULATE (
    AVERAGE ( Data[TAT]),
        FILTER (
            ALLSELECTED ( Data ),
            Data[MonthYear]
                = MIN( ( Data[MonthYear] )
        )
))

I have a slicer made up of the 'Data'[MonthYear] column, however, I've noticed some strange thing regarding the month order, sometimes the MonthYear isn't sorted correctly, which then leads to the wrong Average TAT calculation. 

For example, the Average TAT for March 2018 is 24 as below. Because there is only 1 month selected, that March 2018 acts as the Min and Max months.

However, when I chose both March 2018 & April 2018, the Average TAT for each month was displayed/calculated incorrectly. In this instance, the Min Month TAT is March 2018 which is supposed to be 24 and yet it takes the place of the Max Month which is April's. Moreover, not all months have this manner, sometimes, the Max and Min Months are calculated correctly, sometimes they are not.

I suspect it's the MonthYear calculated column being in Text format that causes the problem, however, I don't know how to go about it.

Does anyone know how to fix this please?

Thank you so much!

[MFelix]

Hi @trdoan ,

Although you have created a new column to make the  sorting and presentation of your data in the slicer you can use the Date field to get your information try to change your measures to the data columns:

Max Month TAT = 
CALCULATE (
    AVERAGE ( Data[TAT]),
        FILTER (
            ALLSELECTED ( Data[Posting Date] ),
            Data[Posting Date]
                = MAX( ( Data[Posting Date] )
        )
))

Min Month TAT = 
CALCULATE (
    AVERAGE ( Data[TAT]),
        FILTER (
            ALLSELECTED ( Data[Posting Date] ),
            Data[Posting Date]
                = MIN( ( Data[Posting Date] )
        )
))

You can use any column on your measure even if the slicers refers to other columns on your data.

Regards,

MFelix 

[trdoan]

Hi @MFelix , thanks for your advice, however, when I changed my measures according to what you'd said, the calculations turned out differently, even though they were for the same month.

Here is what I got:

Do you have any ideas why and how I can fix this?

Thank you so much!