Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
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!
Thank you very much.
This did exactly what I was looking for.
Much Appreciated.
BR,
Kris
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |