Convert calendar quarter to fiscal years

[Anonymous]

Hi PBI community members,

I have imported an excel file and the years are in calendar year format 'Other_Labour force'[Year calendar] and I need to change them to fiscal year format. I've put a table below showing the current format and the format I would like to get them to. Screenshot also included. If anyone can help me that would be great, I find converting these calendar years to fiscal to be conceptually challenging. 

Cheers,

Brent

Current configuration	How I am wanting to display the years
Year calendar	Year fiscal
Dec-2010	2010-11
Mar-2011	2010-11
Jun-2011	2010-11
Sep-2011	2011-12
Dec-2011	2011-12
Mar-2012	2011-12
...	...
Jun-2021	2020-21

[Greg_Deckler · Accepted solution]

@Anonymous Generally you would want to have a fiscal calendar, like my DAX 445 fiscal calendar. DAX Custom 445 Calendar - Microsoft Power BI Community. Then, you would want to take the calendar date and essentially lookup the date the using MAXX or LOOKUPVALUE in the fiscal calendar and return the correct value. For example, you could translate Dec-2010 to 12/1/2010 and look it up or alternatively have a column in your fiscal year that provided a column for the calendar year. Hard to be specific without sample data.

[Anonymous]

Hi @Greg_Deckler and thanks for your advice. 

I do have a date dimension table. But I don't have a column in that Dec-2010 format. I'll have a look at the 445 link you sent and then start thinking about creating a Dec-2010 type of column. 

I'll probably be back with more questions - but until then: thank you!

Cheers,

Brent

[Anonymous]

@Greg_Deckler thanks. I went into my Date Dimension table and duplicated, split and merged a few columns and got the Dec-2010 format I was after. My methods are probably a bit coarse but they seem to work. Thanks for the tip, it saved me lots of confusion.