Forum Discussion

JulianTobon's avatar
JulianTobon
Helper I
9 years ago

Function To Calculate Fiscal Week

Hi all,   I want to calculate the number of weeks based on the date of sale, I used the formula:  week = WEEKNUM(Scorecard[SalesDate],2) This works very well but not for what I need, Due to work ...
Need Help
Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
9 years ago

Hi JulianTobon,

 

Do you have a Date table that contains a column showing which dates belong to which Fiscal year?

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    9 years ago

    Hi JulianTobon

     

    This might be getting close....

     

    FY Week Num = IfERROR(DATEDIFF(
            
                CALCULATE(
                    FIRSTDATE('FiscalSales'[Sales Date]),
                    FILTER(
                        ALL('FiscalSales'),
                        'FiscalSales'[Fiscal Year] = EARLIER('FiscalSales'[Fiscal Year]) 
                        && 'FiscalSales'[Sales Date] <= EARLIER('FiscalSales'[Sales Date])
                        )
                     ),'FiscalSales'[Sales Date],WEEK)+1,-1)
    • JulianTobon's avatar
      JulianTobon
      Helper I
      9 years ago

      Hi Phil_Seamark, thanks for your reply.

       

      The result was:

       

       

      As you can see there are calendar weeks that have 2 different fiscal weeks and this should not be so .. for example calendar week 52 should only be fiscal week 26.

       

       

      • Phil_Seamark's avatar
        Phil_Seamark
        Microsoft Employee
        9 years ago

        HI JulianTobon

         

        Should be easy enough to fix.

         

        Do you have a Date/Calendar table where you have 1 row per day?  I notice your sample data seems to skip lots of days.