Sorting custom weekend dates

Thankyou, miTutorials , rajendraongole1 for your response.

Hi rogerdea,

Thankyou for the update.
Please follow the approach outlined below, which may help in resolving the issue:

1.Create a WeekendGroupIndex: This will group Saturday and Sunday under the same numeric index.

WeekendGroupIndex =

IF (

    dim_date_bi[day_name] = "Saturday",

    dim_date_bi[Index],

    IF (

        dim_date_bi[day_name] = "Sunday",

        dim_date_bi[Index] - 1,

        BLANK()

    )

)
2.Create the WeekendPeriod Label: This will return a unique label, such as “13/14 April 2024,” only once per weekend.

WeekendPeriod =
VAR WeekendIndex = dim_date_bi[WeekendGroupIndex]
VAR SatDay = CALCULATE(MAX(dim_date_bi[Day]), FILTER(dim_date_bi, dim_date_bi[Index] = WeekendIndex))
VAR SunDay = CALCULATE(MAX(dim_date_bi[Day]), FILTER(dim_date_bi, dim_date_bi[Index] = WeekendIndex + 1))
VAR MonthName = CALCULATE(MAX(dim_date_bi[month_name]), FILTER(dim_date_bi, dim_date_bi[Index] = WeekendIndex))
VAR YearVal = CALCULATE(MAX(dim_date_bi[year]), FILTER(dim_date_bi, dim_date_bi[Index] = WeekendIndex))
RETURN
IF (
NOT ISBLANK(WeekendIndex),
SatDay & "/" & SunDay & " " & MonthName & " " & YearVal,
BLANK()
)
3.In Power BI Desktop, select WeekendPeriod → Column Tools → Sort by Column, and then choose WeekendGroupIndex.

If you find this response helpful, kindly mark it as the accepted solution and provide kudos. This will assist other community members who may be facing similar queries.

Thank you.