Forum Discussion
Relate Fiscal year with Calendar table
Hello
I have two dates tables that I want to correlate and categorize. In this case I have a table with Fiscal Year starting and ending date and a continous calendar date. I want, for each selling/buying/calendar record assign which FY we are in.
My company frequently changes the Fiscal Year starting and ending date. So i cannot set a formula for this.
How can I set, for example, for 25/03/2024 specify the FY we have been in?
This is my DB for the FY with starting and ending date
Hi Anonymous,
1.) you can enter FY and EndDate manualy by clicking on the GEAR icon next to step name FiscalDates.
2.) you have to refer your date table in step DateTable (if you don't know how, check note below my post)
(there are two dates 31.3.2024 on the picture below, but I've repaired it in the code)
let FiscalDates = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMlbSAVEm+gbG+saGSrE6YFETiKipvoGRvpGFUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [FY = _t, EndDate = _t]), FiscalDatesChangedType = Table.TransformColumnTypes(FiscalDates,{{"FY", Int64.Type}, {"EndDate", type date}}), FiscalDatesGenDate = [ a = Table.FromColumns(Table.ToColumns(Table.Sort(FiscalDatesChangedType, {{"EndDate", Order.Ascending}})) & {{null} & List.Transform(List.RemoveLastN(FiscalDatesChangedType[EndDate], 1), (x)=> Date.AddDays(x,1))}, Value.Type(Table.FirstN(FiscalDatesChangedType, 0) & #table(type table[StartDate=date], {}))), b = Table.AddColumn(a, "Date", each List.Dates([StartDate] ?? #date([FY],1,1), Duration.TotalDays([EndDate] - ([StartDate] ?? #date([FY],1,1)))+1, #duration(1,0,0,0)), type {date}), c = Table.ExpandListColumn(b, "Date"), d = Table.SelectColumns(c, {"Date", "FY"}) ][d], DateTable = Table.FromList(List.Dates(#date(2023,11,1), Duration.TotalDays(#date(2024,5,31) - #date(2023,11,1))+1, #duration(1,0,0,0)), (x)=> {x}, type table[Date=date]), MergedQueries = Table.NestedJoin(DateTable, {"Date"}, FiscalDatesGenDate, {"Date"}, "FiscalDates", JoinKind.LeftOuter), ExpandedFY = Table.ExpandTableColumn(MergedQueries, "FiscalDates", {"FY"}, {"FY"}) in ExpandedFY
3 Replies
Hi Anonymous,
1.) you can enter FY and EndDate manualy by clicking on the GEAR icon next to step name FiscalDates.
2.) you have to refer your date table in step DateTable (if you don't know how, check note below my post)
(there are two dates 31.3.2024 on the picture below, but I've repaired it in the code)
let FiscalDates = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMlbSAVEm+gbG+saGSrE6YFETiKipvoGRvpGFUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [FY = _t, EndDate = _t]), FiscalDatesChangedType = Table.TransformColumnTypes(FiscalDates,{{"FY", Int64.Type}, {"EndDate", type date}}), FiscalDatesGenDate = [ a = Table.FromColumns(Table.ToColumns(Table.Sort(FiscalDatesChangedType, {{"EndDate", Order.Ascending}})) & {{null} & List.Transform(List.RemoveLastN(FiscalDatesChangedType[EndDate], 1), (x)=> Date.AddDays(x,1))}, Value.Type(Table.FirstN(FiscalDatesChangedType, 0) & #table(type table[StartDate=date], {}))), b = Table.AddColumn(a, "Date", each List.Dates([StartDate] ?? #date([FY],1,1), Duration.TotalDays([EndDate] - ([StartDate] ?? #date([FY],1,1)))+1, #duration(1,0,0,0)), type {date}), c = Table.ExpandListColumn(b, "Date"), d = Table.SelectColumns(c, {"Date", "FY"}) ][d], DateTable = Table.FromList(List.Dates(#date(2023,11,1), Duration.TotalDays(#date(2024,5,31) - #date(2023,11,1))+1, #duration(1,0,0,0)), (x)=> {x}, type table[Date=date]), MergedQueries = Table.NestedJoin(DateTable, {"Date"}, FiscalDatesGenDate, {"Date"}, "FiscalDates", JoinKind.LeftOuter), ExpandedFY = Table.ExpandTableColumn(MergedQueries, "FiscalDates", {"FY"}, {"FY"}) in ExpandedFYWhen dealing with date-related issues, we need to create a date table as a dimension table. For the fiscal year, we usually only need to create a calculated column like Fiscal Year in the date table.
Fiscal Year = VAR cur = 'dt'[Date] RETURN CALCULATE(MIN('ft'[CODICE]), 'ft'[INIZIOESER] <= cur, 'ft'[FINEESER] >= cur)
