Forum Discussion

TRK's avatar
TRK
Helper II
4 years ago
Solved

Create Fiscal Year Offset calculation in Power Query Custom Date Table

Hi,   I have a custom calendar created in Power Query - M.   I want to be able to flag the current FISCAL year  (my fiscal runs April 1 - March 31st) - so I need to be able to calculate the Fisc...
Need Help
  • TRK's avatar
    TRK
    4 years ago

    I found a solution.  

    I created the following 3 columns:
    FY End Year:  (provides the end year of the fiscal year)

    = Table.AddColumn(#"Added FY End", "FY Year", each if [MonthNum] > FiscalYearEndMonth
    then [Year] + 1 else [Year])

    Year.Now
    step 1:  (fills column with current date & time now - all same value)

    = Table.AddColumn(#"FY End Year", "Year.Now", each DateTime.LocalNow())
    step 2:  (extract year from this column using transform tab in ribbon: button 'Date' and selecting 'year' - to just have dynamic current year) 

    = Table.TransformColumns(#"Date.Now (Year.Now)",{{"Year.Now", Date.Year, Int64.Type}})

     

    Current FY Offset (The current fiscal year will have 0's)
    = Table.AddColumn(#"Extracted Year", "Current FY Offset", each [FY End Year-[Year.Now])

TRK's avatar
TRK
Helper II
4 years ago

I found a solution.  

I created the following 3 columns:
FY End Year:  (provides the end year of the fiscal year)

= Table.AddColumn(#"Added FY End", "FY Year", each if [MonthNum] > FiscalYearEndMonth
then [Year] + 1 else [Year])

Year.Now
step 1:  (fills column with current date & time now - all same value)

= Table.AddColumn(#"FY End Year", "Year.Now", each DateTime.LocalNow())
step 2:  (extract year from this column using transform tab in ribbon: button 'Date' and selecting 'year' - to just have dynamic current year) 

= Table.TransformColumns(#"Date.Now (Year.Now)",{{"Year.Now", Date.Year, Int64.Type}})

 

Current FY Offset (The current fiscal year will have 0's)
= Table.AddColumn(#"Extracted Year", "Current FY Offset", each [FY End Year-[Year.Now])