Custom fiscal Calendar weeks handling

[Deelip]

Hi All,

I am looking for a help on handling custom weeks in custom fiscal calendar. I tried different approaches but I am not completely upto the solution for this.

My organisation fiscal calendar starts from Aug 1st to July 31st. For this case we will consider my Fiscal year FY23(August 1st 2022 - July 31st 2023). Currently we have a calendar in place having our custom fiscal month end dates, the only problem is to deal with the first month and last month of fiscal year i.e August and July. 

My FY22 starts with week number "31" i.e Aug 1st 2022 to Aug 6th( this is my first week called 31st week). And my year end also ends with week 31 because( july30 2023 and july 31 2023 is a new week and apparently it is falling under week 31).

The main problem arises when I am trying to plot something on week wise and I cannot slice the week correctly because having week number 31 twice in my fiscal year.

"I cannot even sort my weeks when I am plotting it on line chart. Because Q1 has week 31 and Q4 has week 31".

Any ideas on how to deal with this kind of weeks. Please see the screenshot below. Any suggestions on how to set a calendar in power bi like this kind of weeks. Please and thank you. 

FY2023 Calendar: 

Another screenshot of my Fiscal calendar "FY2024":

[v-denglli-msft · Accepted solution]

Hi @ Deelip,

You can use the Calculated column to display custom weeks.

This calculated column will add the year prefix so that the two 31 week can be distinguished.

In the line chart created based on the week, the two 31 weeks show different data due to the addition of the year prefix.

Please see attached pbix for reference.

Best Regards,

Dengliang Li

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

[Deelip]

Thank you @v-denglli-msft 

This worked for me. I just need to figure out the sorting order and then it's all good. I really appreciate your help on this. 

[lbendlin]

Use an external Calendar table (An Excel file on a SharePoint is just fine) and specify the entire fiscal calendar details in that table. 

Wire the reference table into your data model. 

[Deelip]

Thank you sharing your thoughts on this. I will try your approach. Thanks!