Forum Discussion

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

For Year

Year = ((FORMAT('Calendar'[Date],"YYYY") *1) -1) & "-"&FORMAT('Calendar'[Date],"YY")
DemoFour's avatar
DemoFour
Icon for Continued Contributor rankContinued Contributor
6 years ago

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's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 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
      Icon for Continued Contributor rankContinued Contributor
      6 years ago

      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's avatar
        amitchandak
        Icon for Super User rankSuper User
        6 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.