topic Re: Custom Fiscal Calendar in Power Query

Custom Fiscal Calendar

eisbaer99 — Wed, 13 Mar 2024 14:14:17 GMT

Custom-fiscal-calendar-with-pre-defined-date-ranges

I followed the above link which is absolutely perfect for my reporting but I need to know how to do the following please;

Current Month Offset

Current Day Offset

Both would need to feed from the 'End' date.

If I run this from the resulting CustomFiscalDate then the offset runs from the last day of the month each time and not the actual period end date.

Thanks

Re: Custom Fiscal Calendar

m_dekorte — Wed, 13 Mar 2024 20:31:37 GMT

Hi eisbaer99,

Make sure your custom fiscal calendar includes an ID column for months.

Next you can follow the process described here:

Turn any calendar ID column into an Offset column.

I hope this is helpful

Re: Custom Fiscal Calendar

eisbaer99 — Mon, 18 Mar 2024 14:25:02 GMT

Thanks for this, I have followed a few of the subsequent links and pulled the Extended Date Table from Enterprise DNA.

I'm not sure I explained myself properly so apologies as this so far hasn't given me what I need.

Example, in the UK fiscal, 29/01/2024 is the start of P11 and 26/02/2024 is the start of P12, I need to base my period offsets on these dates so say 25/02/2024 (final day of P11) would show as -1 but 26/02/2024 would show as 0.

Re: Custom Fiscal Calendar

m_dekorte — Mon, 18 Mar 2024 14:42:37 GMT

Hi eisbaer99,

Oh no, that is an ISO-8601 type calendar and does not meet your requirements at all.

The LinkedIn post demonstrates a method to transform any ID column into an Offset. By adding an Index column to your "Period table" before expanding all dates, you can create the offset as described.

I hope that clarifies it. If you require further assistance with the implementation, you should provide a sample.

Re: Custom Fiscal Calendar

eisbaer99 — Mon, 18 Mar 2024 15:15:14 GMT

Sadly I'm struggling to follow the guide but happy to attempt a youtube video if there is one?

Else my code is below, tried to upload a PBIX but it wouldn't let me (appreciate the help).

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZZRksQgCESvMjXfWxUlinqWqbn/NTYQbBLM7p8LvTTDC5afzzu/Ut5S2SjR/toTjuX44/39+bxJBfWMUsexHH+o4IgORBNtqZ3H+tqrCsor7YiKRQsVDmVGlNqWehCwGlv0aCenYNHEeEaPduYRFboYQ8BbzkEwxHhGj3YyBYucdBCqKNqPHVEiZ53EVNQtUVSQeM+w/Oj9PLqL4zhNSihB6jEFbIMvNxwNUZk2B4uiFs0tWqhQ1aL5z+hBwGpsUdGOYNHU2KIy1xQqdDGeUSo2+HLDUREVoBQHlZQo+SBUXO84GsK029zrHUdBGDiquHDEkXUUdpS5qGAyYVftNv3sRhNMQUo64/OIn2R0BAm7Y3uqVdXRUkQGI6pYm7GU/MN4cmzajKWERXqq1RWIpSgbm6ga0sxMyZrRk+PBTj4K8oHpf1AolrWdmaNkpBYZST8zB4okpiNSTI4qSR2+UWyumqjSSrEiBVRppTiQAqpYq6qjpYAqqlibsRRQRcemzVgKqGKtrlAsBVRRNaSZmQKp6Jj1988cSMVPIp+fccaXk+hRRvodWw4U9dPpkWJ1VLrO7Uaxu2qiqitFRgqo6kIR9+cFVaxV/RKtjiqq2G/S6qiiY/PrtDqqWKv7nVodVVQNv1irkzodXwJvIARA/ACPkQOgRUZ+v7LDY/FqER77CvLfK8i+gtHtsoLsK8h/ryD7CsZaF66X2zKqLivIvoLR8bKC7CsYa11WkH0Fo+qyguwryA6vI4TNa/9sXvPNW2SXzWsOrz1u3uU1KPfwnq/w5PFnKTwJ4219vgAthXchLfAOu5nC4zDWqtpMN8eO4wJvIIVnYnRs0sxM4a0Ya3V9GloKD8aoGoon+7zsWbncnzIwy+HluK8UZWJTNp+Pi+zcHcuBopru7+/3Fw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Period Start Date End Date Weeks in Period Days in Period" = _t]),
#"Removed blank rows" = Table.SelectRows(
Source,
each ([Period Start Date End Date Weeks in Period Days in Period] <> "")
),
#"Changed Type to Text" = Table.TransformColumnTypes(
#"Removed blank rows",
{{"Period Start Date End Date Weeks in Period Days in Period", type text}}
),
#"Split Column" = Table.SplitColumn(
#"Changed Type to Text",
"Period Start Date End Date Weeks in Period Days in Period",
Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv),
{
"Period Start Date End Date Weeks in Period Days in Period.1",
"Period Start Date End Date Weeks in Period Days in Period.2",
"Period Start Date End Date Weeks in Period Days in Period.3",
"Period Start Date End Date Weeks in Period Days in Period.4",
"Period Start Date End Date Weeks in Period Days in Period.5"
}
),
#"Changed Field Type" = Table.TransformColumnTypes(
#"Split Column",
{
{"Period Start Date End Date Weeks in Period Days in Period.1", Int64.Type},
{"Period Start Date End Date Weeks in Period Days in Period.2", type date},
{"Period Start Date End Date Weeks in Period Days in Period.3", type date},
{"Period Start Date End Date Weeks in Period Days in Period.4", Int64.Type},
{"Period Start Date End Date Weeks in Period Days in Period.5", Int64.Type}
}
),
#"Renamed Headers" = Table.RenameColumns(
#"Changed Field Type",
{
{"Period Start Date End Date Weeks in Period Days in Period.1", "Period"},
{"Period Start Date End Date Weeks in Period Days in Period.2", "Start Date"},
{"Period Start Date End Date Weeks in Period Days in Period.3", "End Date"},
{"Period Start Date End Date Weeks in Period Days in Period.4", "Weeks in Period"},
{"Period Start Date End Date Weeks in Period Days in Period.5", "Days in Period"}
}
)
in
#"Renamed Headers"

Re: Custom Fiscal Calendar

m_dekorte — Mon, 18 Mar 2024 16:19:19 GMT

Thanks for sharing that eisbaer99

Give this a go

UPDATE your table needed to be sorted first.

let TodaysRec = ExpandDate{ [Date = Date.From( DateTimeZone.FixedUtcNow()) ]}? ?? (error "Today's date is not included in the date table range"), Source = Table.FromRows( Json.Document( Binary.Decompress( Binary.FromText( "fZZRksQgCESvMjXfWxUlinqWqbn/NTYQbBLM7p8LvTTDC5afzzu/Ut5S2SjR/toTjuX44/39+bxJBfWMUsexHH+o4IgORBNtqZ3H+tqrCsor7YiKRQsVDmVGlNqWehCwGlv0aCenYNHEeEaPduYRFboYQ8BbzkEwxHhGj3YyBYucdBCqKNqPHVEiZ53EVNQtUVSQeM+w/Oj9PLqL4zhNSihB6jEFbIMvNxwNUZk2B4uiFs0tWqhQ1aL5z+hBwGpsUdGOYNHU2KIy1xQqdDGeUSo2+HLDUREVoBQHlZQo+SBUXO84GsK029zrHUdBGDiquHDEkXUUdpS5qGAyYVftNv3sRhNMQUo64/OIn2R0BAm7Y3uqVdXRUkQGI6pYm7GU/MN4cmzajKWERXqq1RWIpSgbm6ga0sxMyZrRk+PBTj4K8oHpf1AolrWdmaNkpBYZST8zB4okpiNSTI4qSR2+UWyumqjSSrEiBVRppTiQAqpYq6qjpYAqqlibsRRQRcemzVgKqGKtrlAsBVRRNaSZmQKp6Jj1988cSMVPIp+fccaXk+hRRvodWw4U9dPpkWJ1VLrO7Uaxu2qiqitFRgqo6kIR9+cFVaxV/RKtjiqq2G/S6qiiY/PrtDqqWKv7nVodVVQNv1irkzodXwJvIARA/ACPkQOgRUZ+v7LDY/FqER77CvLfK8i+gtHtsoLsK8h/ryD7CsZaF66X2zKqLivIvoLR8bKC7CsYa11WkH0Fo+qyguwryA6vI4TNa/9sXvPNW2SXzWsOrz1u3uU1KPfwnq/w5PFnKTwJ4219vgAthXchLfAOu5nC4zDWqtpMN8eO4wJvIIVnYnRs0sxM4a0Ya3V9GloKD8aoGoon+7zsWbncnzIwy+HluK8UZWJTNp+Pi+zcHcuBopru7+/3Fw==", BinaryEncoding.Base64 ), Compression.Deflate ) ), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Period Start Date End Date Weeks in Period Days in Period" = _t] ), #"Removed blank rows" = Table.SelectRows( Source, each ([Period Start Date End Date Weeks in Period Days in Period] <> "") ), #"Changed Type to Text" = Table.TransformColumnTypes( #"Removed blank rows", {{"Period Start Date End Date Weeks in Period Days in Period", type text}} ), #"Split Column" = Table.SplitColumn( #"Changed Type to Text", "Period Start Date End Date Weeks in Period Days in Period", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), { "Period Start Date End Date Weeks in Period Days in Period.1", "Period Start Date End Date Weeks in Period Days in Period.2", "Period Start Date End Date Weeks in Period Days in Period.3", "Period Start Date End Date Weeks in Period Days in Period.4", "Period Start Date End Date Weeks in Period Days in Period.5" } ), #"Changed Field Type" = Table.TransformColumnTypes( #"Split Column", { {"Period Start Date End Date Weeks in Period Days in Period.1", Int64.Type}, {"Period Start Date End Date Weeks in Period Days in Period.2", type date}, {"Period Start Date End Date Weeks in Period Days in Period.3", type date}, {"Period Start Date End Date Weeks in Period Days in Period.4", Int64.Type}, {"Period Start Date End Date Weeks in Period Days in Period.5", Int64.Type} } ), SortRows = Table.Buffer( Table.Sort(#"Changed Field Type",{{"Period Start Date End Date Weeks in Period Days in Period.2", Order.Ascending}})), #"Renamed Headers" = Table.RenameColumns( SortRows, { {"Period Start Date End Date Weeks in Period Days in Period.1", "Period"}, {"Period Start Date End Date Weeks in Period Days in Period.2", "Start Date"}, {"Period Start Date End Date Weeks in Period Days in Period.3", "End Date"}, {"Period Start Date End Date Weeks in Period Days in Period.4", "Weeks in Period"}, {"Period Start Date End Date Weeks in Period Days in Period.5", "Days in Period"} } ), AddPeriodID = Table.AddIndexColumn(#"Renamed Headers", "Period ID", 1, 1, Int64.Type), AddListDates = Table.AddColumn( AddPeriodID, "Date", each List.Dates([Start Date], [Days in Period], Duration.From(1)), type {date} ), ExpandDate = Table.ExpandListColumn(AddListDates, "Date"), AddPeriodOffset = Table.AddColumn(ExpandDate, "Period Offset", each [Period ID] - TodaysRec[Period ID], Int64.Type) in AddPeriodOffset

I hope this is helpful

Re: Custom Fiscal Calendar

eisbaer99 — Tue, 19 Mar 2024 09:53:02 GMT

That is absolutely perfect, thank you ever so much for your help and patience.

I'll compare to my original code and see what was changed, hopefully then I can understand it next time.