Sorting custom weekend dates

[rogerdea]

I have created a text column for labelling a weekend (see result in Weekend Days Only visual).  I hadn't realised once i managed to do this that i would be unable to sort this new field by another column (eg date or week) as the new column has duplicates in.

Is there any way around this?  Is there anyway to get my weekends in text format ordered by date?  Or should i abandon my idea for being able to list weekends only in a visual like this?

WeekendPeriod = 

VAR DayNum = dim_date_bi[Day]
VAR CurrentIndex = dim_date_bi[Index]
VAR MonthNow = CONCATENATE(" ", dim_date_bi[month_name])
VAR MonthPrev = CONCATENATE(" ", CALCULATE(FIRSTNONBLANK(dim_date_bi[month_name], 1), FILTER(dim_date_bi, dim_date_bi[Index] = CurrentIndex - 1)))
VAR YearNow = CONCATENATE(" ", dim_date_bi[year])
VAR YearMinus = CONCATENATE(" ",CALCULATE(MAX(dim_date_bi[year]), FILTER(dim_date_bi, dim_date_bi[Index] = CurrentIndex -1)))
VAR DayNumMinus = CALCULATE(MAX(dim_date_bi[Day]), FILTER(dim_date_bi, dim_date_bi[Index] = CurrentIndex -1))
VAR DayNumPlus = CALCULATE(MAX(dim_date_bi[Day]), FILTER(dim_date_bi, dim_date_bi[Index] = CurrentIndex +1))

RETURN

IF(dim_date_bi[day_name] = "Saturday", CONCATENATE(CONCATENATE(CONCATENATE(CONCATENATE(DayNum,"/"), DayNumPlus),MonthNow), YearNow),
IF(dim_date_bi[day_name] = "Sunday", CONCATENATE(CONCATENATE(CONCATENATE(CONCATENATE(DayNumMinus,"/"), DayNum),MonthPrev), YearMinus),
""))

I

[v-pnaroju-msft]

Hi rogerdea,

We are following up to see if your query has been resolved. Should you have identified a solution, we kindly request you to share it with the community to assist others facing similar issues.

If our response was helpful, please mark it as the accepted solution and provide kudos, as this helps the broader community.

Thank you.

[v-pnaroju-msft]

Hi rogerdea,

We wanted to check in regarding your query, as we have not heard back from you. If you have resolved the issue, sharing the solution with the community would be greatly appreciated and could help others encountering similar challenges.

If you found our response useful, kindly mark it as the accepted solution and provide kudos to guide other members.

Thank you.

[v-pnaroju-msft]

Hi rogerdea,

We have not received a response from you regarding the query and were following up to check if you have found a resolution. If you have identified a solution, we kindly request you to share it with the community, as it may be helpful to others facing a similar issue.

If you find the response helpful, please mark it as the accepted solution and provide kudos, as this will help other members with similar queries.

Thank you.

[v-pnaroju-msft · Accepted solution]

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.

[miTutorials]

Go to the Data View - Select the Weekend Period Column - Under Column tool 'Sort By Column' select the Date Field and you will be done! 

Check out the tutorial below if you would like to !

How to Custom Sort in Power BI | MiTutorials | #powerbi #PowerBITutorial

[rogerdea]

This is the problem i have.  As the code creates a duplicate value per weekend, it won't le tme unfortunately.

[rajendraongole1]

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.

[rogerdea]

Thank you for your help.  I still get this error, and the label only shows one of the dates: