Forum Discussion
Tweaking Financial Calendar
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:
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
)
)
Hi MKPartner
I have attached a sample PBIX that uses a financial calendar along with the DAX logic.
Hope this helps !!
Thank You.
19 Replies
- ZanquetaSuper User
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 🌀.
- v-aatheequeCommunity Support
Hi MKPartner
Following up to confirm if the earlier responses addressed your query. If not, please share your questions and we’ll assist further. - MKPartnerHelper II
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.
Thank you
- v-aatheequeCommunity Support
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
- MKPartnerHelper II
This is my current data:
Year No Day of the year Qtr Week of Month Month no Month no P Week Week WK Year WC_Month WC_Date Month Index (related) Week_Value Day of Week 8 358 4 5 12 P12 52 WK52 2025 grudzień 2025 2025P12WK52 2 1 1 8 359 4 5 12 P12 52 WK52 2025 grudzień 2025 2025P12WK52 2 0 2 8 360 4 5 12 P12 52 WK52 2025 grudzień 2025 2025P12WK52 2 0 3 8 361 4 5 12 P12 52 WK52 2025 grudzień 2025 2025P12WK52 2 0 4 8 362 4 5 12 P12 52 WK52 2025 grudzień 2025 2025P12WK52 2 0 5 8 363 4 5 12 P12 52 WK52 2025 grudzień 2025 2025P12WK52 2 0 6 8 364 4 5 12 P12 52 WK52 2025 grudzień 2025 2025P12WK52 2 0 7 9 1 1 1 1 P01 1 WK01 2026 styczeń 2026 2026P01WK01 1 1 1 9 2 1 1 1 P01 1 WK01 2026 styczeń 2026 2026P01WK01 1 0 2 9 3 1 1 1 P01 1 WK01 2026 styczeń 2026 2026P01WK01 1 0 3 9 4 1 1 1 P01 1 WK01 2026 styczeń 2026 2026P01WK01 1 0 4 9 5 1 1 1 P01 1 WK01 2026 styczeń 2026 2026P01WK01 1 0 5 9 6 1 1 1 P01 1 WK01 2026 styczeń 2026 2026P01WK01 1 0 6 9 7 1 1 1 P01 1 WK01 2026 styczeń 2026 2026P01WK01 1 0 7 9 8 1 2 1 P01 2 WK02 2026 styczeń 2026 2026P01WK02 1 1 1 I would like to include Week 01 which is currently in 2026 into 2025.
Date Year No Day of the year Qtr Week of Month Month no Month no P Week Week WK Year WC_Month Month Index (related) Week_Value Day of Week 21/12/2025 8 358 4 5 12 P12 52 WK52 2025 December 20255 2 1 1 22/12/2025 8 359 4 5 12 P12 52 WK52 2025 December 2025 2 0 2 23/12/2025 8 360 4 5 12 P12 52 WK52 2025 December 2025 2 0 3 24/12/2025 8 361 4 5 12 P12 52 WK52 2025 December 2025 2 0 4 25/12/2025 8 362 4 5 12 P12 52 WK52 2025 December 2025 2 0 5 26/12/2025 8 363 4 5 12 P12 52 WK52 2025 December 2025 2 0 6 27/12/2025 8 364 4 5 12 P12 52 WK52 2025 December 2025 2 0 7 28/12/2025 9 1 1 1 1 P01 1 WK01 2025 December 2025 1 1 1 29/12/2025 9 2 1 1 1 P01 1 WK01 2025 December 2025 1 0 2 30/12/2025 9 3 1 1 1 P01 1 WK01 2025 December 2025 1 0 3 31/12/2025 9 4 1 1 1 P01 1 WK01 2025 December 2025 1 0 4 01/01/2026 9 5 1 1 1 P01 1 WK01 2025 December 2025 1 0 5 02/01/2026 9 6 1 1 1 P01 1 WK01 2025 December 2025 1 0 6 03/01/2026 9 7 1 1 1 P01 1 WK01 2025 December 2025 1 0 7 04/01/2026 9 8 1 2 1 P01 2 WK02 2026 January 2026 1 1 1
- ryan_mayuSuper User
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.