cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
trdoan Member
Member

MonthYear column isn't sorted correctly

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 NameTATPosting Date
A14522/05/2018
A5005/06/2018
A5512/06/2018
A12527/02/2018
C2330/04/2018
B1505/02/2018
C4527/04/2018
B1603/01/2018
C1922/03/2018
B8511/01/2018
C6201/02/2018
C2022/01/2018
C2308/03/2018
C2415/02/2018
C2514/03/2018
C2906/04/2018
A6313/04/2018
C6602/05/2018
C3530/04/2018
B3030/04/2018
B3120/04/2018
B3227/04/2018
B4112/02/2018
B2512/03/2018
A2530/03/2018
C2516/03/2018
B2626/03/2018
A811/05/2018
C611/06/2018
A1420/04/2018
A1123/04/2018
A1527/07/2018
C1811/07/2018
C11923/07/2018
B3725/07/2018
B3027/07/2018
B3102/07/2018
B4506/07/2018
A1429/08/2018
A1630/08/2018
A6530/08/2018
C6009/08/2018
C6513/08/2018
C6729/08/2018
B6213/08/2018
B6707/08/2018
B6929/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.

Mar2018.JPG

 

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.

 

Mar2018wrong.JPG

 

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!

2 REPLIES 2
Super User
Super User

Re: MonthYear column isn't sorted correctly

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 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Highlighted
trdoan Member
Member

Re: MonthYear column isn't sorted correctly

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:

wrong1.JPG

 

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

 

Thank you so much!