Accounting Period

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:

1. Create a new query based on your date dimension table.

2. 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
   FiscalYear
   
   

    

   Add 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
   FiscalMonthName
   
   

    

   Add 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
   Period
   
   

   1. Close 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.