Forum Discussion

MKPartner's avatar
MKPartner
Helper II
7 months ago
Solved

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
            )
)

 

 

19 Replies

  • 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-aatheeque's avatar
    v-aatheeque
    Community 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.

  • 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 

      • MKPartner's avatar
        MKPartner
        Helper II

        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

         

  • 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.