Power BI | Creating calculated column from date type column in YYYY Q format. E.g., 2018 Q3

Question

We have a date column and I would like to create a new column in Power BI in YYYY Q(quarter number) format.

For example, if the date is 11/21/2017 then the new column should store 2017 Q4. Later we would like to use the newly calculated column as a slicer on our report.

What is the best approach to achieve this?
For example please find the sample data below.
11/21/2018 will be 2018 Q4 in new column
10/12/2017 will be 2017 Q4 in new column
02/11/2016 will be 2016 Q1 in new column
07/10/2018 will be 2018 Q3 in new column

Answer 1

You should be able to do this using FORMAT where TableName[ColumnName] is the table column where your dates are:

= FORMAT(TableName[ColumnName], "yyyy \Qq")

Here the \ is an escape character so it knows to interpret Q literally, yyyy is the year, and q is the quarter number.

Answer 2

I don't believe that Bower BI comes with a preset Quarter slicer. The way my organization achieves this is by creating rollups of three-month increments. Basically building our own quarter variable and using that.

Our calculated field equates to something similar to

CALCULATE(SUM('TableName'[TargetColumn]),
 FILTER(
    ALL( 'TableName'[YearMonthNumber] ),
         'TableName'[YearMonthNumber] 
          = EARLIER ( 'TableName'[YearMonthNumber] ) – 3)

It will sum the values of the specified column from the previous three months. With a few IF-ELSE statements you can modify it for other periods rather than the most recent three-month timespan. You can refer to this Dax Pattern article for more information

Frankly, if you have access to the data it may be easier to create a row that is the sum of other columns in the period you want rather within your data server rather than Power BI. Plus, by formatting it in your server you can pass it to other services than just Power BI.