Forum Discussion

pvanolinda's avatar
pvanolinda
Frequent Visitor
10 years ago
Solved

Fiscal Year Calculated Columns

Hi!   I created this caculated table:       I'm trying to configure some payroll reports for a client whose fiscal year started on Sunday June 19, 2016. I would like to create a calculate...
Need Help
  • Anonymous's avatar
    Anonymous
    10 years ago

    pvanolinda

     

    With due respect to Treb, the proposed solution by offsetting the week number will work well for year 2016. When you hit the year 2017 with 19/06/2017 as the start of new fiscal year the formula for week number will fail.  

     

    trebgatte correct me if I am wrong.

     

    The solution proposed by me is a generic one and will work for any year and also any starting fiscal year date.  

     

    Attaching the screen shot for your reference.

     

trebgatte's avatar
trebgatte
Icon for Most Valuable Professional rankMost Valuable Professional
10 years ago

So really, it's just an offset. So you can define a custom Fiscal Week Number column where the condition is as follows.

 

if [Week Number] > 25 then [Week Number] - 25 else [Week Number] + 27

 

This should calculate your week number correctly, though the exact date will change from year to year.

 

For the pay period, you could use the IsOdd or IsEven condition get every other week.

 

Hope this helps.

Treb Gatte, Business Solutions MVP | @tgatte | Blog | CIO Magazine Blog

  • pvanolinda's avatar
    pvanolinda
    Frequent Visitor
    10 years ago

    Hi Treb - this works great! I used this command:

     

    FiscalWeekNumber = IF([Week Number]>25,[Week Number]-25,[Week Number]+27)

     

    I'm not quite sure what the syntax for the Pay Period would be, using the IsOdd or IsEven. Can you post an example?

     

    Thanks,

    Pete

    • Anonymous's avatar
      Anonymous
      Not applicable
      10 years ago

      pvanolinda

       

      With due respect to Treb, the proposed solution by offsetting the week number will work well for year 2016. When you hit the year 2017 with 19/06/2017 as the start of new fiscal year the formula for week number will fail.  

       

      trebgatte correct me if I am wrong.

       

      The solution proposed by me is a generic one and will work for any year and also any starting fiscal year date.  

       

      Attaching the screen shot for your reference.

       

      • trebgatte's avatar
        trebgatte
        Icon for Most Valuable Professional rankMost Valuable Professional
        10 years ago

        Anonymous, true the date would change, though I was shooting for consistency with the specific week of the year. Fiscal calendars are typically week/quarter specific, where the beginning of week 25 happens to be 6/19 this year. The approach I took was simply to offset the week number so that Fiscal week one is now calendar week 25.

         

        Make sense?

         

        Treb Gatte | Business Solutions MVP | Coffee Break Business Intelligence Videos