Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
pvanolinda
Frequent Visitor

Fiscal Year Calculated Columns

Hi!

 

I created this caculated table:

 

 

Calendar.png

 

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 calculated column that show week numbers for their fiscal year starting from that day (week 1 would be from Sunday June 19 to Saturday June 25). I want to create a second column that shows 2 week payroll periods, starting from the same date so I can match them up with staff time entries. I've Googled this to death and can't figure out how to do it. If someone could help I would greatly appreciate it!

 

Thanks - Pete

 

1 ACCEPTED SOLUTION

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

 

FiscalYrWeek.GIF

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

View solution in original post

6 REPLIES 6
CheenuSing
Community Champion
Community Champion

@pvanolinda

 

I am assuming that you want to find the the weeknumber with starting of fiscal year from 19/06/2016.

 

I see that you have a calendaryear column, date column in your calendar table.

 

Try the following.

 

1. First convert the Date column into date format type DD/MM/YYYY

2. Create a FiscalYear column using the formula

     =If((Month([Date]) >= 6 && Format([Date],"MMDD") >= "0619") , [Calendar Year],[Calendar Year] -1 )

3. Create column StartofYear which sets the starting of the year for each row in the date table as 

    ="19/06/"&Format([FiscalYear],"####")

    This will create the same value for each row and changes for every fiscal year setting it as 19/06/2016,19/06/2017, etc for each

    row depending on the fiscal year value.

4. Create  a column called FiscalYearWeekNumber based on fiscal year starting from "19/06/2016".

    =CEILING((WEEKDAY([StartofYear])+[Date]-[StartofYear])/7,1)

    

This just relies on the difference between the calendar date and day 1 of the FY, and the fact that weeks contain 7 days. The WEEKDAY function factors in what day of the week the FY starts on, and the date difference and CEILING function handle fractional week differences due to the day of the week the date falls on.

 

With this done you will find that for each year starting from 19/06 weeks are numbered based on Sun to Saturday weeks.

 

Try it out 

 

If this works for you please accept it as a solution and also give kudos.

 

Cheers

 

CheenuSing

   

   

     

 

 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

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

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

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

 

FiscalYrWeek.GIF

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

@CheenuSing, 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

CheenUSing - thanks very much for your suggestion!

 

Treb - you are correct - each financial year doesn't start on June 19th. I just need the week numbers to line up from 1-52 each year.

 

On another note, any idea what formula would work for the 2-week time periods?

 

Thanks - Pete

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.