Custom Fiscal Calendar

[eisbaer99]

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

[m_dekorte]

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

[eisbaer99]

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.

[m_dekorte]

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.

[eisbaer99]

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"

[m_dekorte · Accepted solution]

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

[eisbaer99]

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.