Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Date table with fiscal week numbers

Hi    Can i get some advice on how to write a date table with fiscal week number please . our company fiscal week starts on the first week of april and ends on the last week of march the following ...
Need Help
  • mwegener's avatar
    mwegener
    4 years ago

    Hi Anonymous ,

     

    Did I answer your question?
    Please mark my post as solution, this will also help others.
    Please give Kudos for support.

     

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi mwegener 

 

Just tried with the DAX and somehow it only shows the week of 35, Have add column using new column from the data view below is screen shot from BI desktop 

and below is the DAX that i pasted 

 

Have tried to paste the M code but result is pretty simpler. could you please have a look. 

 

Thanks

 

 

  • mwegener's avatar
    mwegener
    Icon for Most Valuable Professional rankMost Valuable Professional
    4 years ago

    Hi Anonymous ,

     

    somehow I misunderstood the requirement, but have a look at the attached file.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi mwegener 

       

       

      Apologies for the late reply as I can only learn this over the weekend . 

       

      I have watched a video on youtube and foloowed steps and done a calander and used your DAX code to get the FW numbers sorted . 

       

      below is the link that i watched 

      Date Dimension in Power BI with Financial or Fiscal Columns - YouTube

       

      will have a play around and see if anything else needs to add . 

       

      Many thanks for your help and much appreciated .

       

      Cheers

      • mwegener's avatar
        mwegener
        Icon for Most Valuable Professional rankMost Valuable Professional
        4 years ago

        Hi Anonymous ,

         

        Did I answer your question?
        Please mark my post as solution, this will also help others.
        Please give Kudos for support.

         

    • Khanna100's avatar
      Khanna100
      Regular Visitor
      1 year ago

      Thanks MWegener, solved my problem. Just a little modification, to force the first day of the Fiscal year to be the start of the first fiscal week.

      Fiscal Week =
      VAR __fw = [Week Number] - 14 + 1
      RETURN IF(__fw<=0,52+__fw,IF(__fw = 1 && 'Date'[Month Number] = 3, 53, __fw))

       

      Update:

      I found out that if the calendar year starts on Sunday the above would have the first day of the new fiscal year falling in the last week of the previous fiscal year. So, I came up with this:

      Fiscal Week =
      VAR __fw = [Week Number] - IF(WEEKDAY(STARTOFYEAR('Date'[Date],1)) = 1, 13, 14) + 1
      RETURN IF(__fw<=0,52+__fw,IF(__fw = 1 && 'Date'[Month Number] = 3, 53, __fw))
       
      Update 2024-10-10: 
      I found out further anomaly where if the year is a Leap Year and it starts on Sunday, the fiscal week would be wrong in some cases.
      Fiscal Week =
      VAR __fw = [Week Number] - IF(WEEKDAY(STARTOFYEAR('Date'[Date],1)) = 1, IF(((MOD(YEAR('Date'[Date]),4) = 0 && MOD(YEAR('Date'[Date]),100) <> 0) || MOD(YEAR('Date'[Date]),400) = 0),14,13),14) + 1
      RETURN IF(__fw<=0,IF(((MOD(YEAR('Date'[Date]),4) = 0 && MOD(YEAR('Date'[Date]),100) <> 0) || MOD(YEAR('Date'[Date]),400) = 0),53+__fw,52+__fw),IF(__fw = 1 && 'Date'[Month Number] = 3, 53, __fw))