Calculate Fiscal Year starting in July

[BratKat]

Have columns with Date, Quarters, Year, MonthNo, Month 

 Tried a few calulations per New column to get Quarters starting in July I can use in a Slicer as dropdown or vertical list.

This reported a error (Our table is named Work Items) 

Fiscal Quarter = IF('Work Item'[Date].[Quarter]=3,1,IF('Work Item'[Date].[Quarter]=4,2,IF(Work Item'[Date].[Quarter]=1,3,4)))

If anyone can suggest a calculation to use that would be apperiated.

Thank you 

[v-cgao-msft]

Hi @BratKat ,

Please create a calculated column.

Fiscal Quarter = 
SWITCH(
    QUARTER('Work Items'[Date]),
    1,3,
    2,4,
    3,1,
    2
)

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[BratKat]

Works well, is there a way to get the year to be displayed as well since our dates start in 2022 ? 

Thank you 

[v-cgao-msft · Accepted solution]

Hi @BratKat ,

Please try these:

Fin Year = IF(MONTH('Work Items'[Date]) < 7,YEAR('Work Items'[Date])-1,YEAR('Work Items'[Date]))

FY = "FY " & VALUE('Work Items'[Fin Year]-1) & "-" & VALUE('Work Items'[Fin Year])

Qtr Year = "Q" & 'Work Items'[Fiscal Quarter] & " " & 'Work Items'[FY]

Qtr Year Sort = 'Work Items'[Fin Year]*10+'Work Items'[Fiscal Quarter]

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[FreemanZ]

hi @BratKat 

supposing you have a table like:

Date

6/29/2022

6/30/2022

7/1/2022

7/2/2022

6/30/2023

7/1/2023

7/2/2023

try to add a calculated column like:

FY = 
VAR _year = RIGHT(YEAR([Date]),2)
RETURN
IF(
    MONTH([date])>=7,
    "FY"&_year&_year+1,
    "FY"&_year-1&_year
)

it worked like:

[BratKat]

Tried your suggestion, however did not work as expected. 

My table name is Work Items and my column with dates is named Team Date 01 

So using your example my slicer looks like this 

Which is not what I am trying to achive, hoping would look more like yours. Here is my column

FY =

VAR _year = RIGHT(YEAR('Work Items'[Team Date 01].[Date]),2)

RETURN

IF(

    MONTH('Work Items'[Team Date 01].[Date])>=7,

    "FY"&_year&_year+1,

    "FY"&_year-1&_year

[FreemanZ]

hi @BratKat 

This might be helpful:

https://community.powerbi.com/t5/DAX-Commands-and-Tips/New-Measure-for-Fiscal-year/m-p/3183966