Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Learn from the best! Meet the four finalists headed to the FINALS of the Power BI Dataviz World Championships! Register now

Reply
MKPartner
Helper II
Helper II

Tweaking Financial Calendar

‎01-21-2026 03:16 AM

Good morning All, 

 

Our company has change financial calendar. This means that January is starting since 5th of January (calendar week 02) and month closed is in 30th of January (calendar week 05). My calendar looks like below: 

 

MKPartner_0-1768994048422.png

 

Could do you help me to tweak range 28/12/2025 - 03/01/2026 as week 53 in 2025 ? Rest of previous dates are correct. 

 

COQ Calendar = 
var _date = date(2018,1,7)
var _st = _date +-1*if(WEEKDAY(_date)<7,WEEKDAY(_date),WEEKDAY(_date)-7)
var _cal = CALENDAR(Date(2018,1,7), TODAY()+3)
return ADDCOLUMNS( _cal
,"Year No" , QUOTIENT( DATEDIFF(Minx(_cal, [Date]), [Date], day) ,364)+1
,"Day Of Year" , Mod( DATEDIFF(Minx(_cal, [Date]), [Date], day) ,364)+1
,"Day Of Week" , Mod( DATEDIFF(Minx(_cal, [Date]), [Date], day) ,7)+1
,"Qtr" , Mod(QUOTIENT( DATEDIFF(Minx(_cal, [Date]), [Date], day) ,91),4) +1
,"Week of Month" , var _1 = mod(Mod(QUOTIENT( DATEDIFF(Minx(_cal, [Date]), [Date], day) ,7),52),13) 
        var _2  = if(_1=12, 5, mod(_1,4) +1) 
           return _2
,"Month no" , var _1 = mod(Mod(QUOTIENT( DATEDIFF(Minx(_cal, [Date]), [Date], day) ,7),52),13) 
        var _2  = if(_1=12, 3, QUOTIENT( _1,4) +1) 
           return Mod(QUOTIENT( DATEDIFF(Minx(_cal, [Date]), [Date], day) ,91),4)*3 +  _2
,"Month no P" , var _1 = mod(Mod(QUOTIENT( DATEDIFF(Minx(_cal, [Date]), [Date], day) ,7),52),13) 
        var _2  = if(_1=12, 3, QUOTIENT( _1,4) +1) 
           return 
           IF(Mod(QUOTIENT( DATEDIFF(Minx(_cal, [Date]), [Date], day) ,91),4)*3 +  _2 < 10,
                "P0"& Mod(QUOTIENT( DATEDIFF(Minx(_cal, [Date]), [Date], day) ,91),4)*3 +  _2,
                "P"& Mod(QUOTIENT( DATEDIFF(Minx(_cal, [Date]), [Date], day) ,91),4)*3 +  _2)
,"Week" , Mod(QUOTIENT( DATEDIFF(Minx(_cal, [Date]), [Date], day) ,7),52)+1
,"Week WK" , IF(Mod(QUOTIENT( DATEDIFF(Minx(_cal, [Date]), [Date], day) ,7),52)+1<10,
                "WK0"& Mod(QUOTIENT( DATEDIFF(Minx(_cal, [Date]), [Date], day) ,7),52)+1,
                "WK"& Mod(QUOTIENT( DATEDIFF(Minx(_cal, [Date]), [Date], day) ,7),52)+1)
,"Year" , var yearfull = QUOTIENT( DATEDIFF(Minx(_cal, [Date]), [Date], day) ,364)+1
        RETURN
            SWITCH(TRUE(),
            yearfull = 1, 2018,
            yearfull = 2, 2019,
            yearfull = 3, 2020,
            yearfull = 4, 2021,
            yearfull = 5, 2022,
            yearfull = 6, 2023,
            yearfull = 7, 2024,
            yearfull = 8, 2025,
            yearfull = 9, 2026,
            yearfull = 10, 2027,
            yearfull = 11, 2028,
            yearfull = 12, 2029
            )
)

 

 

Labels:
Message 1 of 20
803 Views
0
19 REPLIES 19
ryan_mayu
Super User
Super User

‎02-02-2026 01:34 AM

@MKPartner 

not clear about your request. what if we goes to the next year? always start at Jan 5th?

 

could you pls provide the expected output?

maybe you can put the output in an excel and upload the file.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Message 20 of 20
695 Views
0
MKPartner
Helper II
Helper II

‎01-30-2026 04:55 AM

I have bit understood my needs in this case. 

 

Week number are OK. Our financial calendar was change for 2026 and January is since week 2 to week 5. 

 

Could you please help to tweak above code to include week 01 into 2025 year ? I have tried to read susgested links but I'm doing something wrong. 

 

MKPartner_0-1769777674671.png

 

Thank you 

Message 4 of 20
735 Views
0
v-aatheeque
Community Support
Community Support
In response to MKPartner

‎02-02-2026 12:59 AM

Hi @MKPartner 

To better understand Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information. Do not include anything that is unrelated to the issue or question.

How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Message 5 of 20
700 Views
0
MKPartner
Helper II
Helper II
In response to v-aatheeque

‎02-02-2026 07:11 AM

This is my current data: 

Year NoDay of the yearQtrWeek of MonthMonth noMonth no PWeekWeek WKYearWC_MonthWC_DateMonth Index (related)Week_ValueDay of Week
83584512P1252WK522025grudzień 20252025P12WK52211
83594512P1252WK522025grudzień 20252025P12WK52202
83604512P1252WK522025grudzień 20252025P12WK52203
83614512P1252WK522025grudzień 20252025P12WK52204
83624512P1252WK522025grudzień 20252025P12WK52205
83634512P1252WK522025grudzień 20252025P12WK52206
83644512P1252WK522025grudzień 20252025P12WK52207
91111P011WK012026styczeń 20262026P01WK01111
92111P011WK012026styczeń 20262026P01WK01102
93111P011WK012026styczeń 20262026P01WK01103
94111P011WK012026styczeń 20262026P01WK01104
95111P011WK012026styczeń 20262026P01WK01105
96111P011WK012026styczeń 20262026P01WK01106
97111P011WK012026styczeń 20262026P01WK01107
98121P012WK022026styczeń 20262026P01WK02111

 

I would like to include Week 01 which is currently in 2026 into 2025. 

 

DateYear NoDay of the yearQtrWeek of MonthMonth noMonth no PWeekWeek WKYearWC_MonthMonth Index (related)Week_ValueDay of Week
21/12/202583584512P1252WK522025December 20255211
22/12/202583594512P1252WK522025December 2025202
23/12/202583604512P1252WK522025December 2025203
24/12/202583614512P1252WK522025December 2025204
25/12/202583624512P1252WK522025December 2025205
26/12/202583634512P1252WK522025December 2025206
27/12/202583644512P1252WK522025December 2025207
28/12/202591111P011WK012025December 2025111
29/12/202592111P011WK012025December 2025102
30/12/202593111P011WK012025December 2025103
31/12/202594111P011WK012025December 2025104
01/01/202695111P011WK012025December 2025105
02/01/202696111P011WK012025December 2025106
03/01/202697111P011WK012025December 2025107
04/01/202698121P012WK022026January 2026111

 

Message 6 of 20
683 Views
0
ryan_mayu
Super User
Super User
In response to MKPartner

‎02-02-2026 05:08 PM

@MKPartner 

since you only provide  a part of your data. I am not sure how to do this for the other years.

just based on the sample data you provided.

you can try to create columns

 

Column =
var _date=minx(FILTER('Table','Table'[Date]>=EARLIER('Table'[Date])&&'Table'[Day of the year]=7),'Table'[Date])
return if('Table'[Day of the year]<=7,year(_date)-1,year('Table'[Date]))
 
Column 2 = if('Table'[Day of the year]<=7 ,"December " & 'Table'[Column],FORMAT('Table'[Date],"mmmm")&" " & year('Table'[Date]))
 
11.png
 
pls see the attachment below




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Tweaking Financial Calendar.pbix
Message 7 of 20
664 Views
0
MKPartner
Helper II
Helper II
In response to ryan_mayu

‎02-05-2026 03:25 AM

Thank you for your help. We are pretty OK. This means that 2026 year is OK as I expected, but previous year which where OK, no are NOK. Some weeks are overlapping for two months. 

Screen for Feb 2026: 

MKPartner_0-1770289294020.png

 

Screen for Feb 2025 -> week 9 in 2025 was in March 2025

 
 

Screen for March 2025 -> week 14 in 2025 was in April 2025

bi ex.png

Actual calendar and expected in excel.

 

Actual and Expected calendars 

Message 9 of 20
574 Views
0
ryan_mayu
Super User
Super User
In response to MKPartner

‎02-05-2026 05:06 PM

@MKPartner 

you highlighted two columns. I can find out the difference, however, can't understand the logic.

i created one column for you, pls see the attachment below.

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Calendar-v2.pbix
Message 10 of 20
530 Views
0
MKPartner
Helper II
Helper II
In response to ryan_mayu

‎02-05-2026 10:34 PM

Thank you but everything was change before December 2025 in WC_Month column. 

 

My logic is to keep everything the same since January 2018 until December 2025. 
December 2025 in WC_column needs to cover WK01 in January 2026 so this still needs to be December 2025. I'd like to start with January 2026 in WC_Month column since WK02 2026 starting from Sunday 04/01/2026.


Message 11 of 20
484 Views
0
ryan_mayu
Super User
Super User
In response to MKPartner

‎02-06-2026 12:53 AM

@MKPartner 

still not clear why 1/25-1/31 display 2026 Feb?

could you pls clarify this logic?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Message 12 of 20
465 Views
0
MKPartner
Helper II
Helper II
In response to ryan_mayu

‎02-06-2026 05:54 AM

Our fiscal calendar was changed on the end of last year. We are starting each week on Sunday and finish on Saturday. 

 

In our fiscal calendar for 2026 like week 53 of 2025 but I cannot have week 53. I need to have week 01: 

Our fiscal calendar: 

- week 01 2026 (28/12/2025 - 03/01/2026) is still December 2025
- week 02 to week 05 - Jan 2026
- week 06 to week 09 - Feb 2026

- week 10 to week 14 - Mar 2026

- week 15 to week 18 - Apr 2026

- week 19 to week 22 - May 2026

- week 23 to week 27 - Jun 2026

- week 28 to week 31 - Jul 2026

- week 32 to week 35 - Aug 2026

- week 36 to week 40 - Sep 2026

- week 41 to week 44 - Oct 2026

- week 45 to week 48 - Nov 2026

- week 49 to week 52 - Dec 2026

Message 13 of 20
447 Views
0
ryan_mayu
Super User
Super User
In response to MKPartner

‎02-06-2026 06:26 AM

@MKPartner 

pls see if this is what you want





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Calendar-v2.pbix
Message 14 of 20
439 Views
MKPartner
Helper II
Helper II
In response to ryan_mayu

‎02-17-2026 11:33 PM

Here is excel file with calendar which exactly need with DAX. 

 

https://www.transfernow.net/dl/20260218wiNfXESd

Thank you for support. 

Message 17 of 20
229 Views
0
v-aatheeque
Community Support
Community Support
In response to MKPartner

‎02-19-2026 02:48 AM

Hi @MKPartner 

I have attached a sample PBIX that uses a financial calendar along with the DAX logic.

Hope this helps !!
Thank You.



 

financial.pbix
Message 18 of 20
204 Views
0
v-aatheeque
Community Support
Community Support
In response to v-aatheeque

‎02-23-2026 09:11 PM

Hi @MKPartner 

Could you please let us know if any of the suggestions shared above helped to resolve the issue on your end ?

Message 19 of 20
141 Views
0
MKPartner
Helper II
Helper II
In response to ryan_mayu

‎02-17-2026 10:00 PM

Hello, It's still not what I expected. Anywaya I have clarified fiscal calendar with Finance Team. 

 

Everything until end of 2025 is OK. Change is applied only for first quarter of 2026. This means that first quarter is 5-4-5. Then January 2026 is starting in week 01 which is starting 28/12/2025 and finish 31/01/2025 

 

Rest of quarters are 4-4-5: 


- week 01 to week 05 - Jan 2026 - 5 weeks
- week 06 to week 09 - Feb 2026 - 4 weeks

- week 10 to week 14 - Mar 2026 - 5 weeks

- week 15 to week 18 - Apr 2026 - 4 weeks

- week 19 to week 22 - May 2026 - 4 weeks

- week 23 to week 27 - Jun 2026 - 5 weeks

- week 28 to week 31 - Jul 2026 - 4 weeks

- week 32 to week 35 - Aug 2026 - 4 weeks

- week 36 to week 40 - Sep 2026 - 5 weeks

- week 41 to week 44 - Oct 2026 - 4 weeks

- week 45 to week 48 - Nov 2026 - 4 weeks

- week 49 to week 01/2027 - Dec 2026 - 5 weeks

Message 16 of 20
244 Views
v-aatheeque
Community Support
Community Support
In response to ryan_mayu

‎02-11-2026 04:49 AM

Hi @MKPartner 

As suggested above by @ryan_mayu , has the issue been resolved?

Message 15 of 20
341 Views
0
v-aatheeque
Community Support
Community Support
In response to ryan_mayu

‎02-05-2026 03:09 AM

Hi @MKPartner 

Following up to confirm if the earlier responses addressed your query by@ryan_mayu .If anything is still unclear, we’ll be happy to provide additional support.

Message 8 of 20
577 Views
0
v-aatheeque
Community Support
Community Support

‎01-27-2026 03:36 AM

Hi @MKPartner 

Following up to confirm if the earlier responses addressed your query. If not, please share your questions and we’ll assist further.

Message 3 of 20
747 Views
0
Zanqueta
Super User
Super User

‎01-21-2026 07:33 AM

Hi @MKPartner,

You mention that all dates prior to this are correct.  The main issue is that your current code assumes that every year has exactly 364 days (52 weeks).

"Year No" = QUOTIENT( DATEDIFF(Minx(_cal, [Date]), [Date], day) ,364)+1
"Week"    = Mod(QUOTIENT( DATEDIFF(Minx(_cal, [Date]), [Date], day) ,7),52)+1
``
When you introduce a 53rd week, this assumption no longer holds, because the 2025 financial year is no longer 364 days long. Below is one way to adjust the logic while keeping most of your original approach, treating 2025 as a special case with 53 weeks.

 

recommended reed post by @amitchandak 

Solved: Financial Year Calendar - Microsoft Fabric Community

 

If this response was helpful in any way, I’d gladly accept a 👍much like the joy of seeing a DAX measure work first time without needing another FILTER.

Please mark it as the correct solution. It helps other community members find their way faster (and saves them from another endless loop 🌀.

If this response was helpful in any way, I’d gladly accept a kudo.
Please mark it as the correct solution. It helps other community members find their way faster.
Connect with me on LinkedIn

Message 2 of 20
795 Views
0

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

Share feedback directly with Fabric product managers, participate in targeted research studies and influence the Fabric roadmap.

February Power BI Update Carousel

Power BI Monthly Update - February 2026

Check out the February 2026 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

View All