Forum Discussion
Create Fiscal Year Offset calculation in Power Query Custom Date Table
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 , Try a new column like
if [Date] >= #date(if Date.Month(Date.From(DateTime.FixedLocalNow())) >3 Then Date.Year(Date.From(DateTime.FixedLocalNow()))-1 else Date.Year(Date.From(DateTime.FixedLocalNow())),4,1) && [Date] >= #date(if Date.Month(Date.From(DateTime.FixedLocalNow())) >3 Then Date.Year(Date.From(DateTime.FixedLocalNow())) else Date.Year(Date.From(DateTime.FixedLocalNow()))+1 ,3,31) then 1 else 0
That doesn't seem to work. I fixed the 2 Then to then and it didn't accept && so I change that to and but everything in the column returned 0