Fiscal Year Table (Newbie Looking for Help)

[TapZxK]

Hi Guys,

I'm fairly new to Power BI, I have just created my first Calendar Table using CALENDARAUTO and I have set my FY End month as (8) August.

Dates = CALENDARAUTO(08)

I have also created columns that represent:

Day No = Dates[Date].[Day]

Day Name = FORMAT(Dates[Date], "DDDD")

Month No = Dates[Date].[MonthNo]

Month Name = Dates[Date].[Month]

Year = Dates[Date].[Year]

I would like to create a further columns representing the below, but I don't know how. 😞

FY Quarter No = appearing like this (Q1, Q2, Q3, Q4)

FY = appearing like this (FY18, FY19, FY20, FY21)

FY Q & Year = appearing like so (Q1 FY20, Q2 FY20, Q3 FY20, Q4 FY20)

FY Quarters in my organization roll like so:

Q1 = September, October, November

Q2 = December, January, February

Q3 = March, April, May

Q4 = June, July, August

I would greatly appreciate your help.

BR,

Kris

[amitchandak · Accepted solution]

Calculate these three, You can rest from these three

Start of year =STARTOFYEAR('Date'[Date],"8/31")
end of year =ENDOFYEAR('Date'[Date],"8/31")

Qtr No = QUOTIENT(datediff(STARTOFYEAR('Date'[Date],"8/31"),'Date'[Date],Month),3)+1

Like

FY Quarter No = "Q" & Date[Qtr No]
FY = "FY" & format(Date[Start of year],"YY")
or
FY = "FY" & format(Date[end of year],"YY")

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[Anonymous]

Using these formulas how do I then extract a fiscal period that would include YYYYMM for each period.  

IE:  FY=2020 begins on 9/1/2019.  

I am looking to create this:

202001 (Sep 2019)

212002 (Oct 2019)......and so forth for each fiscal year.

Thanks!

[TapZxK]

Thank you very much. 

This did exactly what I was looking for. 

Much Appreciated. 

BR,

Kris

[Anonymous]

Hi Kris,

Is there a possibility you could attach a sample file with your calendar table in there? I'd like to start and possibly help out where you have left of.

Regards

Eswar