Forum Discussion
Accounting Period
Hi,
I hope you will be able to help with an issue I have been wrestling with for a while.
I have a date dimension table that contains all the normal date-related columns, however, I need to add a fiscal/accounting period/month/year and this is where I'm getting stuck. The fiscal period is based on the following logic.
- The fiscal year starts in September and ends in August.
- The end of the fiscal month ends on the last Saturday of the month, however, if the last week of the month overlaps with the next month the first Saturday of the next month end the period. For example, The November 2022 period started on the 30th October and ended on 3rd December. The December 2022 period starts on the 4th of December and ends on the 31st December.
Can anyone help me with a solution?
Thanks,
Greg.
To create a fiscal period/month/year based on the logic you provided, you can use Power Query or DAX in Power BI.
Here is an example of a Power Query solution:
Create a new query based on your date dimension table.
Add a custom column with the following formula to calculate the fiscal year:
let
Source = [Date],
Year = if Month(Source) >= 9 then Year(Source) else Year(Source) - 1,
FiscalYear = "FY" & Text.From(Year)
in
FiscalYearAdd another custom column with the following formula to calculate the fiscal month:
let
Source = [Date],
Month = if Month(Source) >= 9 then Month(Source) - 8 else Month(Source) + 4,
LastDayOfMonth = Date.EndOfMonth(Source),
LastSaturdayOfMonth = LastDayOfMonth - Duration.Days(Date.DayOfWeek(LastDayOfMonth, Day.Saturday)),
NextMonth = Date.AddMonths(Source, 1),
FirstSaturdayOfNextMonth = Date.StartOfMonth(NextMonth) + Duration.Days(6 - Date.DayOfWeek(Date.StartOfMonth(NextMonth), Day.Saturday)),
FiscalMonth = if LastSaturdayOfMonth >= FirstSaturdayOfNextMonth then Month + 1 else Month,
FiscalMonthName = "FM" & Text.PadStart(Text.From(FiscalMonth), 2, "0")
in
FiscalMonthNameAdd a third custom column with the following formula to calculate the fiscal period:
let
Source = [Date],
Month = if Month(Source) >= 9 then Month(Source) - 8 else Month(Source) + 4,
LastDayOfMonth = Date.EndOfMonth(Source),
LastSaturdayOfMonth = LastDayOfMonth - Duration.Days(Date.DayOfWeek(LastDayOfMonth, Day.Saturday)),
NextMonth = Date.AddMonths(Source, 1),
FirstSaturdayOfNextMonth = Date.StartOfMonth(NextMonth) + Duration.Days(6 - Date.DayOfWeek(Date.StartOfMonth(NextMonth), Day.Saturday)),
FiscalMonth = if LastSaturdayOfMonth >= FirstSaturdayOfNextMonth then Month + 1 else Month,
Year = if Month(Source) >= 9 then Year(Source) else Year(Source) - 1,
Period = "P" & Text.From(Year) & Text.PadStart(Text.From(FiscalMonth), 2, "0")
in
PeriodClose and apply the query.
This will add three new columns to your date dimension table: FiscalYear, FiscalMonth, and FiscalPeriod.
Alternatively, you can use DAX formulas in a calculated column or measure to achieve the same result. However, the logic to calculate the fiscal month may be more complex in DAX, especially when dealing with overlapping weeks.
3 Replies
Can anyone help?
Thanks.
To create a fiscal period/month/year based on the logic you provided, you can use Power Query or DAX in Power BI.
Here is an example of a Power Query solution:
Create a new query based on your date dimension table.
Add a custom column with the following formula to calculate the fiscal year:
let
Source = [Date],
Year = if Month(Source) >= 9 then Year(Source) else Year(Source) - 1,
FiscalYear = "FY" & Text.From(Year)
in
FiscalYearAdd another custom column with the following formula to calculate the fiscal month:
let
Source = [Date],
Month = if Month(Source) >= 9 then Month(Source) - 8 else Month(Source) + 4,
LastDayOfMonth = Date.EndOfMonth(Source),
LastSaturdayOfMonth = LastDayOfMonth - Duration.Days(Date.DayOfWeek(LastDayOfMonth, Day.Saturday)),
NextMonth = Date.AddMonths(Source, 1),
FirstSaturdayOfNextMonth = Date.StartOfMonth(NextMonth) + Duration.Days(6 - Date.DayOfWeek(Date.StartOfMonth(NextMonth), Day.Saturday)),
FiscalMonth = if LastSaturdayOfMonth >= FirstSaturdayOfNextMonth then Month + 1 else Month,
FiscalMonthName = "FM" & Text.PadStart(Text.From(FiscalMonth), 2, "0")
in
FiscalMonthNameAdd a third custom column with the following formula to calculate the fiscal period:
let
Source = [Date],
Month = if Month(Source) >= 9 then Month(Source) - 8 else Month(Source) + 4,
LastDayOfMonth = Date.EndOfMonth(Source),
LastSaturdayOfMonth = LastDayOfMonth - Duration.Days(Date.DayOfWeek(LastDayOfMonth, Day.Saturday)),
NextMonth = Date.AddMonths(Source, 1),
FirstSaturdayOfNextMonth = Date.StartOfMonth(NextMonth) + Duration.Days(6 - Date.DayOfWeek(Date.StartOfMonth(NextMonth), Day.Saturday)),
FiscalMonth = if LastSaturdayOfMonth >= FirstSaturdayOfNextMonth then Month + 1 else Month,
Year = if Month(Source) >= 9 then Year(Source) else Year(Source) - 1,
Period = "P" & Text.From(Year) & Text.PadStart(Text.From(FiscalMonth), 2, "0")
in
PeriodClose and apply the query.
This will add three new columns to your date dimension table: FiscalYear, FiscalMonth, and FiscalPeriod.
Alternatively, you can use DAX formulas in a calculated column or measure to achieve the same result. However, the logic to calculate the fiscal month may be more complex in DAX, especially when dealing with overlapping weeks.
Hi,
That is great. I will take a look.
Thanks,
Greg.