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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ALEKSGOD
Helper I
Helper I

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

@ALEKSGOD ,

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-Calend...
https://medium.com/chandakamit/cheat-sheet-calendar-of-any-standard-month-just-one-variable-apart-5e...
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

View solution in original post

ryan_mayu
Super User
Super User

@ALEKSGOD 

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

@ALEKSGOD 

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!




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!




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!




unfortunately, I can't 😞

ALEKSGOD
Helper I
Helper I

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

@ALEKSGOD ,

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-Calend...
https://medium.com/chandakamit/cheat-sheet-calendar-of-any-standard-month-just-one-variable-apart-5e...
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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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