Business period offset with multiple business calendars

[Sjoerd_g]

Hi there,

I have a dataset containing data for multiple clients, and all these clients have their business period calendars, which I have combined in a Business Dates table in the Query Editor. I have offset columns for calendar Months and Weeks, but I'd like one based on the Business Period, but because each client has different periods, I am struggling to get the correct result.

I tried several solutions, but none gave the desired result because of the multiple companies. Some have 4-4-5 week periods, some have 13 periods in a year, there are quarters (instead of periods), and most have different start and end dates.

This was the latest try (credits: https://forum.enterprisedna.co/t/fiscal-period-offset-in-power-query/8651 )

IdentifyCurrentDate = Table.SelectRows(#"Changed Type5", each ([Date] = CurrentDate)),
CurrentPeriod = IdentifyCurrentDate{0}[#"Year-Period"],
BufferTable = Table.Buffer(Table.Distinct( #"Changed Type5"[[#"Year-Period"]])),
#"Filtered Rows" = Table.SelectRows(BufferTable, each [#"Year-Period"] <> null and [#"Year-Period"] <> ""),
InsertPeriodOffset = Table.AddColumn(#"Changed Type5", "Period Offset", each
if _[#"Year-Period"] > CurrentPeriod = false then -Table.RowCount( Table.SelectRows( #"Filtered Rows", (IT) => IT[#"Year-Period"] > _[#"Year-Period"] and IT[#"Year-Period"] < CurrentPeriod))
else if _[#"Year-Period"] = CurrentPeriod then 0
else Table.RowCount( Table.SelectRows( #"Filtered Rows", (IT) => IT[#"Year-Period"] < _[#"Year-Period"] and IT[#"Year-Period"] > CurrentPeriod )), type number),
#"Changed Type6" = Table.TransformColumnTypes(InsertPeriodOffset,{{"Period Offset", Int64.Type}})

However, this finds the first Year-Period and then bases the offset on this value alone, and then applies the offset to all the periods across all companies, but since businesses are in different periods, this does not help.

Here is an example of 2 companies in completely different periods. One is in their 2022 Financial Year and in period 11, whereas the second is in 2023 period 6.

Desired result

I'd like to achieve when the current date falls between the period start and end date to return a 0 and every period be offset from that, keeping in mind this is different for each company (c_id). The expected results are in the above image.

I removed the company names from the dataset (for obvious reasons) but have a company id column called c_id which can help segment the periods if needed.

A sample .pbix and the .csv tables with business periods and weeks can be downloaded here: https://we.tl/t-NOAKakeMCn 

Please let me know if you need more information.

Thanks in advance.

[Sjoerd_g · Accepted solution]

I resorted to a CSV table built with R and load that into PBi.

[v-xiaotang]

Hi @Sjoerd_g 

Thanks for reaching out to us.

when I open the link, there is no download button😂 could you share it again? (my account is company account, hence I cannot log in that website

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[Sjoerd_g]

Sorry, the link expired. Here is a new link: https://we.tl/t-NOAKakeMCn 

[vanessafvg]

you haven't really explained the issue you are having.  Why is what you have not working?  what are the results you are getting and what are you expecting?  please show examples of the issue and give what you are expecting in that scenario.

[Sjoerd_g]

The expected results are in the image - sorry if that wasn't clear. I updated the original post.