Forum Discussion

DemoFour's avatar
DemoFour
Icon for Continued Contributor rankContinued Contributor
7 years ago
Solved

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 Ju...
Need Help
  • amitchandak's avatar
    amitchandak
    7 years ago

    Position of * 1 is wrong

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

    It should be after format close )

  • DemoFour's avatar
    DemoFour
    6 years ago

     

    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

     

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
7 years ago

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's avatar
DemoFour
Icon for Continued Contributor rankContinued Contributor
7 years ago

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's avatar
    DemoFour
    Icon for Continued Contributor rankContinued Contributor
    6 years ago

     

    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