Combine Years for Quarter

[DemoFour]

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

[amitchandak · Accepted solution]

Position of * 1 is wrong

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

It should be after format close )

[amitchandak]

For Year

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

[DemoFour]

@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

[amitchandak · Accepted solution]

Position of * 1 is wrong

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

It should be after format close )

[DemoFour]

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

[amitchandak]

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. 

[DemoFour]

@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

[DemoFour · Accepted solution]

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