Skip to main content
cancel
Showing results for 
Search instead 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

Reply
BratKat
Microsoft Employee
Microsoft Employee

Calculate Fiscal Year starting in July

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

 

DateColumns.png

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

 

BratKat_0-1683310978859.png

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

 

Thank you 

 

 

1 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]

vcgaomsft_0-1684292576763.png

 

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

View solution in original post

6 REPLIES 6
v-cgao-msft
Community Support
Community Support

Hi @BratKat ,

Please create a calculated column.

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

vcgaomsft_0-1683600195501.png

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

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

 

Thank you 

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]

vcgaomsft_0-1684292576763.png

 

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
Super User
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:

FreemanZ_1-1683456728316.png

 

BratKat
Microsoft Employee
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 

 

 

BratKat_0-1683572847704.png

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
Super User
Super User

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.