Fiscal Year

Hi  

Tks for your reply 

Just as an awarness i use the same query which initially was just showing Previous fiscal year, Current Fiscal year and Next fiscal year on a label. 

We wanted to improve by adding more for example if they select SELECT ALL.   this is the adjustment we did to it, since then not able to make the query work. 

Here's the query

let
// Your existing data source
Source = [Calendar Table.Date],

// Ensure the date column is of type date
SourceWithDateType = Table.TransformColumnTypes(Source, {{"Current Renewal EndDate", type date}}),

// Debugging Step: Check the transformed source
DebugSource = SourceWithDateType,

// Filter rows based on the date comparison
FilteredRows = Table.SelectRows(DebugSource, each [Current Renewal EndDate] > #date(2023, 4, 1)),

// Debugging Step: Check the filtered rows
DebugFilteredRows = FilteredRows,

// Get the current year
CurrentYear = Date.Year(DateTime.LocalNow()),

// Find the maximum end date
MaxEndDate = List.Max(DebugFilteredRows[Current Renewal EndDate]),

// Get the year of the maximum end date
EndYear = Date.Year(MaxEndDate),

// Define the start year
StartYear = 2023,

// Add FiscalYearLabel column
AddFiscalYearLabel = Table.AddColumn(DebugFilteredRows, "FiscalYearLabel", each
let
SelectedYear = Date.Year([Current Renewal EndDate]),
Label = if SelectedYear = CurrentYear - 1 then "Previous Fiscal Year"
else if SelectedYear = CurrentYear then "Current Fiscal Year"
else if SelectedYear = CurrentYear + 1 then "Next Fiscal Year"
else if SelectedYear = CurrentYear + 2 then "2 Years from Now"
else "Other"
in
Label
),

// Create a string representing all fiscal years
AllFiscalYears = Text.From(StartYear) & "-" & Text.From(EndYear),

// Add AllLabel column
AddAllLabel = Table.AddColumn(AddFiscalYearLabel, "AllLabel", each
if [FiscalYearLabel] = "Other" then "ALL - " & AllFiscalYears else [FiscalYearLabel]
),

// Add BeginFiscalYear and EndFiscalYear columns
AddFiscalYearRange = Table.AddColumn(AddAllLabel, "BeginFiscalYear", each
if [FiscalYearLabel] = "Select All" then StartYear else Date.Year([Current Renewal EndDate])),
AddFiscalYearRangeEnd = Table.AddColumn(AddFiscalYearRange, "EndFiscalYear", each
if [FiscalYearLabel] = "Select All" then EndYear else Date.Year([Current Renewal EndDate]))
in
AddFiscalYearRangeEnd

Yes i did check and confirm the contract renewal enddate is formated as a date