RANKX for business day

[Fred_PT]

Calendar Table

I need your help.

I have a Calendar table with dates and I need to create a column to sort only by working day within each month, as the example I attached (yellow column).

I know it involves the RANKX formula, but I'm having a hard time knowing how.

Thanks

[Zubair_Muhammad · Accepted solution]

Hi @Fred_PT

Try this Calculated column

RANK =
VAR myrank =
    RANKX (
        FILTER (
            CalendarTable,
            CalendarTable[YEAR] = EARLIER ( CalendarTable[YEAR] )
                && CalendarTable[Month] = EARLIER ( CalendarTable[Month] )
                && CalendarTable[Working Day] IN { 1, 2, 3, 4, 5 }
        ),
        CalendarTable[Date],
        ,
        ASC,
        DENSE
    )
RETURN
    IF ( CalendarTable[Working Day] IN { 1, 2, 3, 4, 5 }, myrank, 0 )

[Fred_PT]

Worked perfectly!!!

Thank you very much for your quick response!!

Best regards,

Fred

[Zubair_Muhammad]

@Fred_PT

Sample File attached here