Fiscal year and month

[Anonymous]

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),
Thanks for help in advance,
Aleks

[amitchandak · Accepted solution]

@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

[ryan_mayu · Accepted solution]

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

pls see the attachment below

[Anonymous]

I have the same problem with If function:


When I opened file from you there are also errors. 

[ryan_mayu]

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

[Anonymous]

This is the error:

[ryan_mayu]

could you pls provide the pbix file?

[Anonymous]

unfortunately, I can't 😞

[Anonymous]

Hi @amitchandak 
This formula doesn't work. Generally, I have a problem with If formula.

when I add <10 I can't continue with ResultifTrue.

[amitchandak · Accepted solution]

@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