Fiscal week number starting from monday

[Sagar_Apte]

Hi All,

I need to create week number for fiscal calendar goes from Apr to Mar. Also week number should start from monday.

For eg. In FY 15-16, my first week should start from 07/Apr/15 as first monday of this fiscal year starts from this day. Similarly it should apply for other years as well.

Note: When I have partial financial year data week number should not start with 1, it should start with actual week number for that fiscal year although it has partial data.

Currently I tried below logic which gives me week number from start of the financial year i.e 01/Apr which I don't want. I know there are lot many resources for this but could not find anything related to what I want.

Div(Date-YearStart(Date,0,4),7)+1 as FiscalWeek

@sunny_talwar  @Kushal_Chawda @Vegar

[sunny_talwar]

Would you be able to provide sample data and the output you expect to see from it?

[Vegar]

I'm not sure if I understood all the details in your specification, but you could probably get to your correct week number using week() to some extent. 

Maybe like this: week(AddMonths([Date],-3) ) 

[Sagar_Apte]

@Vegar @sunny_talwar  let's say I have fiscal year 14/15(Apr 2014 to Mar 2015). Now I need week number like below

Date                                       WeekNumber

1 Apr 2014 - 6 Apr 2014      1    (as week starts from monday only 6 days will go to 1st week as 7 is monday)

7 Apr 2014-13 Apr 2014      2

14 Apr 2014 -20 Apr 2014    3

.......

30 Mar 2015 -31 Mar 2015    53 (only two days goes to this week as financial year is ending)

Similarly logic should go for all financial

 I have partial data for fiscal year FY 13/14. i.e  1 Apr 2014 to 31 Mar 2014. In this case wek number should go like below

1 Apr 2014-5 Apr 2014     40 ( as these are part of week 40 of year FY 13/14)

6 Apr 2014-12 Apr 2014    41

.....

31 Mar 2014              53  (only 1 day goes to this week as it is year end)

I hope I am clear now.

[Kushal_Chawda · Accepted solution]

@Sagar_Apte  try below in your calendar script or in table load. Assuming your Date field is properly formatted 

 Ceil((WeekStart(Date,0,0)-YearStart(Date,0,4))/7)+1 as WeekNumber;

[Sagar_Apte]

@Vegar  it is not working as expected. For eg. for FY 14/15,   It's giving week number 1 only for Date 1 Apr 2014 to 4 Apr 2014..whereas I need week number as 1 for  1 Apr 2014 to 6 Apr 2014 as week should start from monday..so from 7 Apr 2014 week 2 will start

[Sagar_Apte]

@Kushal_Chawda  thanks a lot. working as expected