cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
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
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 )



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

View solution in original post

Highlighted
Helper I
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

 

pbi2.PNG

View solution in original post

7 REPLIES 7
Highlighted
Super User IV
Super User IV

Re: Combine Years for Quarter

For Year

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


Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Helper I
Helper I

Re: Combine Years for Quarter

QY.PNG

@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
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 )



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

View solution in original post

Highlighted
Helper I
Helper I

Re: Combine Years for Quarter

@amitchandak 

 

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
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. 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Helper I
Helper I

Re: Combine Years for Quarter

@amitchandak 

 

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

 

QY1.PNG

 

qy2.PNG

Highlighted
Helper I
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

 

pbi2.PNG

View solution in original post

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

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

Community Blog

Community Blog

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

Upcoming Events

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

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