topic Re: Calculate Fiscal Year starting in July in DAX Commands and Tips

Calculate Fiscal Year starting in July

BratKat — Fri, 05 May 2023 18:24:55 GMT

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

Re: Calculate Fiscal Year starting in July

FreemanZ — Sun, 07 May 2023 10:45:41 GMT

hi BratKat

This might be helpful:

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

Re: Calculate Fiscal Year starting in July

FreemanZ — Sun, 07 May 2023 10:52:40 GMT

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:

Re: Calculate Fiscal Year starting in July

BratKat — Mon, 08 May 2023 19:09:16 GMT

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

Re: Calculate Fiscal Year starting in July

Anonymous — Tue, 09 May 2023 02:43:57 GMT

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

Re: Calculate Fiscal Year starting in July

BratKat — Thu, 11 May 2023 14:24:15 GMT

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

Thank you

Re: Calculate Fiscal Year starting in July

Anonymous — Wed, 17 May 2023 03:03:50 GMT

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

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