cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Frequent Visitor

Calendar Table

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

1 ACCEPTED SOLUTION
Community Champion

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 )```

Regards
Zubair

3 REPLIES 3
Community Champion

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 )```

Regards
Zubair

Frequent Visitor

Worked perfectly!!!

Thank you very much for your quick response!!

Best regards,

Fred

Community Champion

@Fred_PT

Sample File attached here

Regards
Zubair

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors