Find the next order type filtered by PoDExternal number and date using a ranking Dax calc

[Hakim34400]

Hi everyone,

My issue is summarize in the screenshot above.

The calc works great for dates (sequential) however for text (order type), it will put text in but sometimes it will be the wrong text. Almost like it is pulling based upon alphabetical order rather than by order date.

Any help would be highly appreciated,

Thank you so much,

Kim

[Hakim34400]

NEXT MAT = 
CALCULATE(
   MIN('1_New Attachments 1'[Order Number.Order Number Level 01.Maintenance Activity (Key)]),
    FILTER(
        ALLEXCEPT(
            '1_New Attachments 1',
            '1_New Attachments 1'[PoD(External).PoD(External) Level 01]
        ),
        '1_New Attachments 1'[FCMP Date.FCMP Date Level 01]
        >EARLIEST('1_New Attachments 1'[FCMP Date.FCMP Date Level 01])&&'1_New Attachments 1'[Rank Date]>EARLIER('1_New Attachments 1'[Rank Date])-1
        )
    )

Sorry forgot to add the code. Thanks again.

[v-yinliw-msft · Accepted solution]

Hi @Hakim34400 ,

The date and the num can be sort directly because they are considered numbers can be directly compared in size.

The text is indeed sorted by default in alphabetical order, so you can sort it by sort the date in the visual.

In the data table, you can get the result but can not sort them, but you can add them in the table visual and sort the result as you want.

Looks like this, I create a table based on your code:

Then I use your code to get the result:

And in the visual, you can select which column do you want.

Hope this helps you.

And here is my PBIX file.

Best Regards,

Community Support Team _Yinliw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.