Forum Discussion
Greetings
Salutations Power BI Community,
I am currently doing Radacads->"Create a Date Dimension in Power BI in 4 Steps – Step 2: Fiscal Columns"
https://radacad.com/create-a-date-dimension-in-power-bi-in-4-steps-step-2-fiscal-columns
Albeit I am perplexed on "Creating Fiscal Columns/Fiscal Period or Month", regarding the "+6/-6"
Any explanation of the data is much appreciated.
Thank you folks,
Regards,
-Jerzee
Hi Jerz,
Thank you for reaching out to the Microsoft Fabric Forum Community.
A special thanks to lbendlin for the quick and helpful response.
That “+6 / -6” part it’s just specific to when the fiscal year starts in July (month 7). In that case.
Months before July (e.g., June = 6) are adjusted using 6 + 6 = 12
Months from July onward (e.g., July = 7) are adjusted using 7 - 6 = 1
The 6 comes from StartOfFiscalYear - 1. so this isn’t a fixed number. If your fiscal year starts in April, for example, you’d use StartOfFiscalYear = 4, and the adjustment would be different.
The general and more accurate formula provided later in the article handles this properly.
if [Month] >= StartOfFiscalYear then
[Month] - (StartOfFiscalYear - 1)
else
[Month] + (12 - StartOfFiscalYear + 1)This dynamically calculates the fiscal month no matter which month your fiscal year begins.
If you found this response helpful, please consider marking it as the accepted solution and giving it a thumbs-up to help others in the community.
Thank you & regards,
Prasanna Kumar
7 Replies
Hi Jerz,
Thank you for reaching out to the Microsoft Fabric Forum Community.
A special thanks to lbendlin for the quick and helpful response.
That “+6 / -6” part it’s just specific to when the fiscal year starts in July (month 7). In that case.
Months before July (e.g., June = 6) are adjusted using 6 + 6 = 12
Months from July onward (e.g., July = 7) are adjusted using 7 - 6 = 1
The 6 comes from StartOfFiscalYear - 1. so this isn’t a fixed number. If your fiscal year starts in April, for example, you’d use StartOfFiscalYear = 4, and the adjustment would be different.
The general and more accurate formula provided later in the article handles this properly.
if [Month] >= StartOfFiscalYear then
[Month] - (StartOfFiscalYear - 1)
else
[Month] + (12 - StartOfFiscalYear + 1)This dynamically calculates the fiscal month no matter which month your fiscal year begins.
If you found this response helpful, please consider marking it as the accepted solution and giving it a thumbs-up to help others in the community.
Thank you & regards,
Prasanna KumarI really appreciate this analysis of this component. This is a steep learning curve. The "Fiscal Month" still is confusing, It is mainly the whole "+6 / -6” and "if [Month] >= StartOfFiscalYear then
[Month] - (StartOfFiscalYear - 1)
else
[Month] + (12 - StartOfFiscalYear + 1)" but I will look over this explanation again.Thank you again for taking the time.
-Jerzee
Which month does your fiscal year start?
From the source ->
"As an example; June 2017 is Fiscal period 12 of the fiscal year 2017. July 2017 is fiscal period 1 of the fiscal year 2018. As you can see the logic above; if calendar month is before fiscal year start month, then we add 6 to the calendar month to get the fiscal year, otherwise; we’ll reduce 6 from it."You take a regular calendar and shift it by the number of months your Fiscal calendar is different. If your FY2018 begins in calendar year 2017 then you shift back, if it starts on 2018 then you shift forward.
CALENDARAUTO(7) would do a positive shift only, I don't think that function supports negative shifts.
From the source ->
"As an example; June 2017 is Fiscal period 12 of the fiscal year 2017. July 2017 is fiscal period 1 of the fiscal year 2018. As you can see the logic above; if calendar month is before fiscal year start month, then we add 6 to the calendar month to get the fiscal year, otherwise; we’ll reduce 6 from it."
