Quarter as Date in parameters

[Anonymous]

Hi AIl

In my powerbi report I used the parameters option to filter a bar chart by day, by month and by year. I would like to do the same by quarter, but power bi doesn't recognize Q1 2022 or Q3 2022 as quarter but only as text. Because in the date/time format there is no quarter option. So when I filter the graph by quarter the bars are not displayed in chronological orders. How can I solve it?

Thanks

[suparnababu8]

QuarterDate = 
DATE(
    YEAR([Date]), 
    SWITCH(
        TRUE(),
        FORMAT([Date], "q") = "1", 1,
        FORMAT([Date], "q") = "2", 4,
        FORMAT([Date], "q") = "3", 7,
        FORMAT([Date], "q") = "4", 10
    ), 
    1
) In your date table, add a calculated column that combines the year and quarter into a date format. Here’s above an example DAX formula:

1. Use the Calculated Column in Your Visual: Drag the QuarterDate column to the axis of your bar chart. This will ensure that the bars are displayed in chronological order.

2. Filter by Quarter: In the “Visualizations” pane, drag the QuarterDate column to the “Filters” section.

[Duinhir]

Thank you suparnababu8,

the sorting problem is solved, and to be honest it could be fine to leave like it is.

Is there any chance to display it instead of the months: Q1, Q2 and so forth?

Below it's how it looks now. I chose "(yyyy-mm)" in the column format, which is the closest to what I'd like to show.

Thank you very much for your support!

Kind regards

Fabio

[ichavarria]

Hi @Anonymous,

It´s difficult to provide you with detailed steps without more information. However, to solve this issue, you can create a calculated column in your date table that will extract the quarter from the date. You can then use this calculated column as a filter in your visualizations.

Here are the steps to create a calculated column:

1. Go to the date table in Power BI and select "New Column" from the "Modeling" tab.

2. In the formula bar, enter the following formula to extract the quarter from the date:

   Quarter = "Q" & CEILING(MONTH([Date])/3) & " " & YEAR([Date])

   This formula will create a new column with the quarter and year information in the format of "Q1 2022", "Q2 2022", etc.

3. Click "Enter" to create the calculated column.

4. Now, you can use this new column as a filter in your visualizations. In the "Visualizations" pane, select your bar chart and drag the "Quarter" column to the "Filters" section.

5. Select the quarters you want to display in your bar chart and click "OK".

With this new calculated column, your bar chart should display the bars in chronological order based on the quarters.

Best regards, 

Isaac Chavarria

If this post helps, then please consider Accepting it as the solution and giving Kudos to help the other members find it more quickly.

[Duinhir]

Hello Isaac, I found your help very useful. My problem is that I have multiple years and when I try to sort them PowerBi sort them alphabetically, because it's set as Text. If I try to modify the "Data Type" in date, then I get an error. How to sort Q12023 - Q2 2023 - ... instead of Q1 2023 - Q1 2024 - Q2 2023 - ... ?

[Bali2022]

Just one thing to add to your formula.  There needs to be a 1 before the close of the CEILING function to designate the "significance" and round the calculated value to the nearest integer:

Quarter = "Q" & CEILING(MONTH([Date])/3, 1) & " " & YEAR([Date])