Forum Discussion

djreyes's avatar
djreyes
Icon for Helper I rankHelper I
3 years ago
Solved

Fiscal year

Hello, 

Fiscal year starts April, I tried the formula below but I am getting an error message:

 

Column 2 = if(QUARTER(PowerBi[Document Date].[Quarter])>=2,QUARTER(PowerBi[Document Date].[Quarter])-1,QUARTER(PowerBi[Document Date].[Quarter])+1)

 

The error message is: Cannot convert Qrt 1 of Type Test to type Date. 

 

I feel like I am missing something. Please help.

 

Thank you!

  • johnt75's avatar
    johnt75
    3 years ago

    The QUARTER function expects a date, but you're passing in the quarter from the date hierarchy. Try

    Column 2 =
IF (
    QUARTER ( PowerBi[Document Date].[Date] ) >= 2,
    QUARTER ( PowerBi[Document Date].[Date] ) - 1,
    QUARTER ( PowerBi[Document Date].[Date] ) + 1
)
  • johnt75's avatar
    johnt75
    3 years ago

    Yep, that should give the right result I think

7 Replies

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    3 years ago

    The QUARTER function expects a date, but you're passing in the quarter from the date hierarchy. Try

    Column 2 =
IF (
    QUARTER ( PowerBi[Document Date].[Date] ) >= 2,
    QUARTER ( PowerBi[Document Date].[Date] ) - 1,
    QUARTER ( PowerBi[Document Date].[Date] ) + 1
)
  • djreyes's avatar
    djreyes
    Icon for Helper I rankHelper I
    3 years ago

    It worked, question though, I am running a report from January to March which is Quarter 1 for calendar year, but in our fiscal year its quarter 4. Its giving me the result of 2 instead of 4. 

    • johnt75's avatar
      johnt75
      Icon for Super User rankSuper User
      3 years ago

      Try

      Column 2 =
VAR CalendarQuarter =
    IF (
        QUARTER ( PowerBi[Document Date].[Date] ) >= 2,
        QUARTER ( PowerBi[Document Date].[Date] ) - 1,
        QUARTER ( PowerBi[Document Date].[Date] ) + 1
    )
VAR FiscalQuarter =
    MOD ( CalendarQuarter + 3, 4 )
RETURN
    FiscalQuarter