Fiscal week not on the first day of month

[Anonymous]

Hi all, 

I'm trying to add Fiscal Week and Month to my date table. It starts on October.

I used blog post by @ChandeepChhabra which I found on a solved question on this website, to generate the Fiscal Week column and it worked but not my desired result.

Question Link - https://community.powerbi.com/t5/Desktop/Creating-a-fiscal-week-column/m-p/556549 

Blog Post Link - https://www.goodly.co.in/calculate-fiscal-week-in-power-bi/

This was the result I get when I used it.

My desired fiscal week is a little different, it is not on the first day of the month. Here's an example:

As you can see, the first week starts on the 3rd of Oct where as the result I got from refering to the post starts on 1st of Oct. If the first 2 days of the month is at the end of the week then it is considered to be the week of previous month.

I would also like to add another column for the Month based on the fiscal week. Below is my desired Dim Date table.

Date	Work Week	Month (WW)
10/1/2021 12:00:00 AM	WW 52	September
10/2/2021 12:00:00 AM	WW 52	September
10/3/2021 12:00:00 AM	WW 1	October
10/4/2021 12:00:00 AM	WW 1	October

[v-jayw-msft · Accepted solution]

Hi @Anonymous ,

Add the following computed columns in sequence.

_year = YEAR('calendar'[Date])

_month = MONTH('calendar'[Date])

_week = WEEKNUM('calendar'[Date])

_count = CALCULATE(COUNT('calendar'[Date]),FILTER('calendar','calendar'[_year]=EARLIER('calendar'[_year])&&'calendar'[_month]=EARLIER('calendar'[_month])&&'calendar'[_week]=EARLIER('calendar'[_week])))

year_start = STARTOFYEAR('calendar'[Date])

fy_start =

var this_year = CALCULATE(MIN('calendar'[Date]),FILTER('calendar','calendar'[_year]=EARLIER('calendar'[_year])&&'calendar'[_month]=10&&'calendar'[_count]=7))

var last_year = CALCULATE(MIN('calendar'[Date]),FILTER('calendar','calendar'[_year]=EARLIER('calendar'[_year])-1&&'calendar'[_month]=10&&'calendar'[_count]=7))

return

IF('calendar'[Date]>=this_year,this_year,last_year)

ft_week = roundup((DATEDIFF('calendar'[fy_start],'calendar'[Date],DAY)+1)/7,0)

WEEKNUM('calendar'[Date]-_diff,1)

Result:

Best Regards,

Jay

[Anonymous]

Hi @v-jayw-msft,

Thanks! This is the result I desired, but there is a problem,

As you can see, the date 1 year before has empty value for fy_start and 1 for ft_week, is there a way to fix this? Maybe by setting the date to infinte (Not sure is this efficient, cuz I'm a newbie)? If there is no solution, then I will just accept this as the solution.

Also may I know what is the use of the last line of code, "WEEKNUM('calendar'[Date]-_diff,1)"? Because I didnt use it.

Really appreciate your effort in solving my issue! Thanks!! 😁

[v-jayw-msft]

Hi @Anonymous ,

You are correct.

These blank values are due to incomplete dates for the previous fiscal year in the calendar table which don't have corresponding values be found. Extending the range of the calendar table will work.

My suggestion is to push up one year for the year range you need. For example, if you want 2020 to 2023, then your calendar will start from 2019. After adding a calculated column, you can filter the blank values.

Best Regards,

Jay

[amitchandak]

@Anonymous ,

Start of Year  =if( MONTH([date]) <10 , date( year([date])-1,10,1) , date( year([date]),10,1))

Start Week Date =  [Date] -1* WEEKDAY([Date],1) +1

FY Year =  year([Start of Year])

Week Start FY = minx(filter(Date , [FY Year] =earlier([FY Year]), [Start Week Date] )

Week Num = Quotient(datediff([Week Start FY],[Start Week Date], day),7) +1

[Anonymous]

Hi @amitchandak ,

I changed the code a little below are my changes:

FY Year = year([Start of Year]) + 1

I added 1 because FY 2022 starts on Oct 2021

Week Start FY = minx(filter('Dim Date' , [FY Year] =earlier([FY Year])), [Start Week Date] )

I added a closing bracket after 'earlier([FY Year])' because there were some error compiling

This is what I got after adding these columns to my date table :

As you can see the first 2 days of Oct 21 is still considered as the first week. My desired output is if the first 2 days of the month is on the end of the week, then it is not considered the day of the month instead it is the day of the previous month.

In this case, the 1st and 2nd of Oct 2021 should be Week 52 of 2021, and Week 1 of 2022 should start on 3rd of Oct 2021

[amitchandak]

@Anonymous , Sorry, My Mistake - Use Week Start Year

FY WEEK Year = year([Start Week Date])

In next one

Week Start FY = minx(filter('Dim Date' , [FY WEEK Year ] =earlier([FY WEEK Year])), [Start Week Date] )

[Anonymous]

Hi @amitchandak ,

I think the previous one is correct because if I change the [Start of Year] to [Start Week Date], the Week No will reset on January of the next year, and it is supposed to reset on October. 

The problem now is that the first 2 days of October should be Week 52 of 2021 and 3rd October should be Week 1 of 2022. I think it should be something like this

Date : 26-30 Sep 2021 and 1-2 Oct 2021

Start of Year : 1 Oct 2020

Start Week Date : 26 Sep 2021

FY Year : 2021

Week Start FY : 27 Sep 2020

Week Num : 53

Date : 3-9 Oct 2021

Start of Year : 1 Oct 2021

Start Week Date : 3 Oct 2021

FY Year : 2022

Week Start FY : 3 Oct 2021

Week Num : 1

Is there a way to check whether 1 Oct or 2 Oct is on Saturday, if so then the FY Year and Start of Year will remain on the previous FY Year and Start of Year?