Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Sjoerd_g
Frequent Visitor

Business period offset with multiple business calendars

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.

Sjoerd_g_0-1662644718604.png

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.

1 ACCEPTED SOLUTION
Sjoerd_g
Frequent Visitor

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

View solution in original post

5 REPLIES 5
Sjoerd_g
Frequent Visitor

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

v-xiaotang
Community Support
Community Support

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

vxiaotang_0-1663316834875.png

 

 

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.

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

vanessafvg
Super User
Super User

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.

 

 





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors