Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Anonymous
Not applicable

Fiscal year and month

Hi,
I would like to combine values from FY22 and FY23 on visualizations. In files I have calendar date. Fiscal year start in October. How can I make a column with Fiscal month and date. 
I want to make sth like this but as u can see dates are messed up. ( I just merged P01 with a year and I think its wrong approach),
example3.PNGThanks for help in advance,
Aleks

2 ACCEPTED SOLUTIONS
amitchandak
Super User
Super User

@Anonymous ,

you need following column

 Period = "P" & format(if(month([Date]) <10, month([Date])+3, month([Date]) -9 ),"00")

 

 Year Period = "P" & format(if(month([Date]) <10, month([Date])+3, month([Date]) -9 ),"00")& "-" & if(month([Date]) <10, Year([Date]), Year([Date]) +1 ) //you can change this logic

 

Sort Year Period = if(month([Date]) <10, Year([Date]), Year([Date]) +1 )*100 + if(month([Date]) <10, month([Date])+3, month([Date]) -9 )

 

mark sort Year Period as sort column of  Year Period

How to Create Sort Column and Solve Related Errors:
https://www.youtube.com/watch?v=KK1zu4MBb-c

 

Creating Financial Calendar - From Any Month
https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calendar-1-5-Power/ba-p/1187441
https://medium.com/chandakamit/cheat-sheet-calendar-of-any-standard-month-just-one-variable-apart-5ee47de6a208
https://amitchandak.medium.com/cheat-sheet-power-query-financial-year-calendar-5ceaacb520f1
DAX Calendar - Standard Calendar, Non-Standard Calendar, 4-4-4 Calendar
https://www.youtube.com/watch?v=IsfCMzjKTQ0&t=145s

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

ryan_mayu
Super User
Super User

@Anonymous 

you can try to create some columns

 

year = year('Table'[Date])
month = month('Table'[Date])
fiscalyear = if('Table'[month]>=10,'Table'[year]+1,'Table'[year])
fiscalmonth = if('Table'[month]>=10,'Table'[month]-9,'Table'[month]+3)
yearmonth = 'Table'[fiscalyear]*100+'Table'[fiscalmonth]
Column = "P"&right("0"&'Table'[fiscalmonth],2)&":"&'Table'[fiscalyear]

1.png

pls see the attachment below

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

8 REPLIES 8
ryan_mayu
Super User
Super User

@Anonymous 

you can try to create some columns

 

year = year('Table'[Date])
month = month('Table'[Date])
fiscalyear = if('Table'[month]>=10,'Table'[year]+1,'Table'[year])
fiscalmonth = if('Table'[month]>=10,'Table'[month]-9,'Table'[month]+3)
yearmonth = 'Table'[fiscalyear]*100+'Table'[fiscalmonth]
Column = "P"&right("0"&'Table'[fiscalmonth],2)&":"&'Table'[fiscalyear]

1.png

pls see the attachment below

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

I have the same problem with If function:
error.png

When I opened file from you there are also errors. ERROR2.png

i didn't see the error message in your pbix file. What's the error message in your own pbix file?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

This is the error:
DIM Date error.png


could you pls provide the pbix file?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

unfortunately, I can't 😞

Anonymous
Not applicable

Hi @amitchandak 
This formula doesn't work. Generally, I have a problem with If formula.
with result.pngwithout result.png
when I add <10 I can't continue with ResultifTrue.

amitchandak
Super User
Super User

@Anonymous ,

you need following column

 Period = "P" & format(if(month([Date]) <10, month([Date])+3, month([Date]) -9 ),"00")

 

 Year Period = "P" & format(if(month([Date]) <10, month([Date])+3, month([Date]) -9 ),"00")& "-" & if(month([Date]) <10, Year([Date]), Year([Date]) +1 ) //you can change this logic

 

Sort Year Period = if(month([Date]) <10, Year([Date]), Year([Date]) +1 )*100 + if(month([Date]) <10, month([Date])+3, month([Date]) -9 )

 

mark sort Year Period as sort column of  Year Period

How to Create Sort Column and Solve Related Errors:
https://www.youtube.com/watch?v=KK1zu4MBb-c

 

Creating Financial Calendar - From Any Month
https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calendar-1-5-Power/ba-p/1187441
https://medium.com/chandakamit/cheat-sheet-calendar-of-any-standard-month-just-one-variable-apart-5ee47de6a208
https://amitchandak.medium.com/cheat-sheet-power-query-financial-year-calendar-5ceaacb520f1
DAX Calendar - Standard Calendar, Non-Standard Calendar, 4-4-4 Calendar
https://www.youtube.com/watch?v=IsfCMzjKTQ0&t=145s

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.