Forum Discussion

macgeorge's avatar
macgeorge
Helper I
3 years ago
Solved

FIscal Year Display in Table

Hello all, apologies, I am new to Power BI and I would really appreciate your help. I am having an issue with the display of fiscal date in the table. I am using DAX below to create the Fiscal Year column and when it displays in the table the FY shows as 2019-20 which is what I want for the quarters and Q1 and Q2 are correct, however for Q3 and Q4 the FY is displayed like this 19-2020, which is wrong. I don't know why.

 

This is the DAX for Fiscal Year:

 

FY =
VAR CY = RIGHT(YEAR('Calendar'[Date]), 4)
VAR LY = RIGHT(YEAR('Calendar'[Date]) -1, 2)
VAR NY = RIGHT(YEAR('Calendar'[Date]) +1, 2)
VAR FiscalYear =
IF(
MONTH('Calendar'[Date]) >= 7,
CY & "-" & NY,
LY & "-" & CY
)
RETURN
FiscalYear
 
This is the DAX for Fiscal Quarter:
 
Fiscal Quarter =
"Q" &
CEILING(
MONTH(EDATE('Calendar'[Date],-6)),
3
) / 3
 
Ideally I want to report on events that occur in FY 20xx-xx (format) and the quarters to be displayed for the corresponding FY from 1 July 20xx to 30 June 20xx
 
This is what it looks like:

 

  • This part : LY & "-" & CY  is going to be "2 chars - 4 chars" so that's what needs to be fixed.

    --

    Also RIGHT should work on a text value but YEAR is going to return a number.  This may work but I would not rely on it.

2 Replies

  • HotChilli's avatar
    HotChilli
    Community Champion

    This part : LY & "-" & CY  is going to be "2 chars - 4 chars" so that's what needs to be fixed.

    --

    Also RIGHT should work on a text value but YEAR is going to return a number.  This may work but I would not rely on it.

    • macgeorge's avatar
      macgeorge
      Helper I

      Thank you HotChilli - that works perfectly. I appreciate your quick response. Cheers