cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Microsoft Employee

## Calculate Fiscal Year starting in July

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

1 ACCEPTED SOLUTION
Community Support

Hi @BratKat ,

``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

6 REPLIES 6
Community Support

Hi @BratKat ,

``````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

Microsoft Employee

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

Thank you

Community Support

Hi @BratKat ,

``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

Super User

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:

Microsoft Employee

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

Super User