Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

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 rece...
Need Help
  • PowerBIDave's avatar
    PowerBIDave
    1 year ago

    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.

     

PowerBIDave's avatar
PowerBIDave
Regular Visitor
1 year ago

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.

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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?

     

    • PowerBIDave's avatar
      PowerBIDave
      Regular Visitor
      1 year ago

      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.