Forum Discussion
Sort Slicer by a date descending
I have a slicer with two columns: quarter and month
Format is 2024-Q1 or 2024-01 respectively.
Now In the slicer I want it to show quarter from most recent to earliest, and months from most recent to earliest too.
When I sort in the slicer options, I can only pick one, when I sort from Column tools by Primary key, i can only sort it earliest to latest. How else I can do that?
This is what I see when I sort it by Quarter, descending - quarters are fine but months are in the reverse order
This is see when I sort by month : months are fine but quarters in reverse order to what i wand
sorting by primary key is only ascending:
Anonymous
It would appear that you've omitted an extra pair of parentheses around the sub-formula.
The two snippets of code you provided should be as follows.
The Quarter number forumla:
Temp_Yr_Quart_Num = 1000000 - (YEAR(dim_date[PK_Date])*100 + QUARTER(dim_date[PK_Date]))Then, the Month number formula:
Temp_Yr_Mth_Num = 1000000 - (YEAR(dim_date[PK_Date])*100 + MONTH(dim_date[PK_Date]))
Used as the sorting coulmns for your quarter and month columns this should work.
Failing that, try creating the "Year Quarter" and "Year Month" fields as described in my previous message.
Hope that helps.
If this answers your question, please mark as a solution so others can find.
6 Replies
- lbendlin
Super User
Change your PK_Date column to subtract the actual date from a date in the future.
Hey lbendlin , thanks for the answer, I am not getting what you mean in your response, can you show me an example please?
- lbendlin
PowerBIDave provided the example
Anonymous
One way to solve the issue is to create calculated columns in your date table similar to the following figure.
You can create the requisite columns using the following DAX code (adapt as necessary).
Year Quarter = YEAR([PK_Date]) & "-Q" & FORMAT ([PK_Date], "q" )
Year Quarter Number = 1000000 - (YEAR([PK_Date]) * 100 + QUARTER([PK_Date]))
Year Month = FORMAT ( [PK_Date], "yyyy" ) & "-" & FORMAT ( [PK_Date], "MM" )
Year Month Number = 1000000 - (YEAR ( [PK_Date] ) * 100 + MONTH ( [PK_Date] ))
Next, sort the "Year Quarter" column by the "Year Quarter Number" column.
Then, sort the "Year Month" column by the "Year Month Number" column.
Your slicer should then appear as follows.
Hope that helps.
If this answers your question, please mark as a solution so others can find.
Hello PowerBIDave thanks for the tip. I did as you mentioned and getting the same error. Below I am showing what I did:
The Quarter number foruma:Temp_Yr_Quart_Num = 1000000 - YEAR(dim_date[PK_Date])*100 + QUARTER(dim_date[PK_Date])Then, the Month number formula:Temp_Yr_Mth_Num = 1000000 - YEAR(dim_date[PK_Date])*100 + MONTH(dim_date[PK_Date])Then I sorted my Quarter field (named QuarterWithYear) by the Quarter number:Also, I sorter the month field (Year_Month) by the month number field:
At this point, my slicer with quarter - month sorts on Quarter, but not on month:
What did I miss?
Anonymous
It would appear that you've omitted an extra pair of parentheses around the sub-formula.
The two snippets of code you provided should be as follows.
The Quarter number forumla:
Temp_Yr_Quart_Num = 1000000 - (YEAR(dim_date[PK_Date])*100 + QUARTER(dim_date[PK_Date]))Then, the Month number formula:
Temp_Yr_Mth_Num = 1000000 - (YEAR(dim_date[PK_Date])*100 + MONTH(dim_date[PK_Date]))
Used as the sorting coulmns for your quarter and month columns this should work.
Failing that, try creating the "Year Quarter" and "Year Month" fields as described in my previous message.
Hope that helps.
If this answers your question, please mark as a solution so others can find.
