Forum Discussion
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.
This is the problem i have. As the code creates a duplicate value per weekend, it won't le tme unfortunately.
Hi rogerdea - You're right — Power BI does not allow a column with duplicate values (like your WeekendPeriod) to be sorted by a column that has different values for the same row (like date).
Create a new column WeekendSortIndex
WeekendSortIndex =
IF (
dim_date_bi[day_name] = "Saturday",
dim_date_bi[Index],
IF (
dim_date_bi[day_name] = "Sunday",
dim_date_bi[Index] - 1, -- Saturday's index
BLANK()
)
)
Modify your WeekendPeriod measure to only return a value on Saturday
WeekendPeriodLabel =
IF (
dim_date_bi[day_name] = "Saturday",
VAR DayNum = dim_date_bi[Day]
VAR DayNumPlus = CALCULATE(MAX(dim_date_bi[Day]), FILTER(dim_date_bi, dim_date_bi[Index] = dim_date_bi[Index] + 1))
VAR MonthNow = " " & dim_date_bi[month_name]
VAR YearNow = " " & dim_date_bi[year]
RETURN DayNum & "/" & DayNumPlus & MonthNow & YearNow,
BLANK()
)
Now you'll only get one unique WeekendPeriodLabel per weekend,Select your WeekendPeriodLabel column in Power BI.
Go to Column Tools > Sort by Column > choose WeekendSortIndex.
Hope this works.
Thank you for your help. I still get this error, and the label only shows one of the dates: