cancel
Showing results for
Did you mean:
Highlighted
Helper I

## Combine Years for Quarter

Appologies if this has been covered but I cant find anything to show how to do this when doing a search!

I have  Quarters that run in the following order:

Jan - March = Q4

April - June = Q1

July - September = Q2

October - December = Q3

I need to build a column that shows the years as follows, in order to filter the visuals by Quarter Year

Date                  Month           Quarter            Year                  QYear

01/01/17               1                       Q4                2017           2016 - 2017

01/01/18               1                       Q4                2018           2017 - 2018

01/01/19               1                       Q4                2019           2018 - 2019

Any help greratfully recived

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User IV

## Re: Combine Years for Quarter

Position of * 1 is wrong

`Year = ((FORMAT('Date'[Date],"YYYY") *1) -1) & "-"&FORMAT('Date'[Date],"YY")`

It should be after format close )

My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Helper I

## Re: Combine Years for Quarter

For completeness of this thread, I solved the issues with a couple of columns for year quarter and FY quarter and then combined the year from the year coloumn to match them up in order to produce slicers.

Year Q (Jan - Dec) = Yr Q = "Q" & INT (  FORMAT (  'DATE'[Date] , "q" )  )

and

FYQ (April - March UK) =

FY Q =
IF (
'DATE'[Month Number] <= 3,
"Q4",
IF (
'DATE'[Month Number] <= 6,
"Q1",
IF (
'DATE'[Month Number] <= 9,
"Q2",
IF (
'DATE'[Month Number] <= 12,
"Q3"
)
)
)
)

then used the following DAX

7 REPLIES 7
Highlighted
Super User IV

## Re: Combine Years for Quarter

For Year

`Year = ((FORMAT('Calendar'[Date],"YYYY") *1) -1) & "-"&FORMAT('Calendar'[Date],"YY")`

My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Helper I

## Re: Combine Years for Quarter

@amitchandak

Thanks for the quick response, but the code whilst its on the lines I was working on (With format ),  is not quite right. Could you check please

Highlighted
Super User IV

## Re: Combine Years for Quarter

Position of * 1 is wrong

`Year = ((FORMAT('Date'[Date],"YYYY") *1) -1) & "-"&FORMAT('Date'[Date],"YY")`

It should be after format close )

My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Helper I

## Re: Combine Years for Quarter

Thanks for that, I was missing the ( ........ *1 ) -1 ) from my coding to get it to work.

To understand the logic , how does this part of the code work?

Highlighted
Super User IV

## Re: Combine Years for Quarter

When we get YYYY from format usually it text. Just to make sure it becomes number I multiplied it with 1. But, You can also try -1 directly.

My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Helper I

## Re: Combine Years for Quarter

Thanks for the explination, I had an play with those numbers to see how it affected the result.

Now I just need to fix the cross over years, as currently it is not giving the corect set of Quarters the correct years.

Q1 should then change to 2017-18 not when the year changes in the year column

Highlighted
Helper I

## Re: Combine Years for Quarter

For completeness of this thread, I solved the issues with a couple of columns for year quarter and FY quarter and then combined the year from the year coloumn to match them up in order to produce slicers.

Year Q (Jan - Dec) = Yr Q = "Q" & INT (  FORMAT (  'DATE'[Date] , "q" )  )

and

FYQ (April - March UK) =

FY Q =
IF (
'DATE'[Month Number] <= 3,
"Q4",
IF (
'DATE'[Month Number] <= 6,
"Q1",
IF (
'DATE'[Month Number] <= 9,
"Q2",
IF (
'DATE'[Month Number] <= 12,
"Q3"
)
)
)
)

then used the following DAX

Announcements

#### August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

#### Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

#### Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

#### Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors