topic Re: Creating Customized Fiscal Week Column in Report Server

Creating Customized Fiscal Week Column

Powereports — Tue, 08 Dec 2020 21:26:08 GMT

Hello Users,

I am trying to create a customized Fiscal week column based on the Fiscal period starting from September - August and week starting on Monday.

My Problem is i am getting partial weeks and really want to get rid of it. As an example by using the below Dax I am getting, 01-Sept-2019 ( Sunday ) as week 1 and 02-Sept-2019 (Monday) as Week 2 , similarly 31-Aug-2020 as Week 54.

Need suggestions as to compute Dax such that we get only full weeks and only 52 weeks in total for year. Then 01-sept-2019 will have no week number and so would be the case with 31-Aug-2020.

Dax Formula used:

Fiscal Week =

VAR WeekStartsOn = "Mon"

VAR FiscalStartMonth = 9

VAR FiscalmaxWeek = 52

--Calculation--

VAR FiscalFirstDay =

IF(

MONTH('Calendar'[Date]) < FiscalStartMonth,

DATE(

YEAR('Calendar'[Date])-1,

FiscalStartMonth,1

DATE(

YEAR('Calendar'[Date]),

FiscalStartMonth,1

)

VAR FilteredTableCount =

COUNTROWS(

FILTER(

SELECTCOLUMNS(

GENERATESERIES(

FiscalFirstDay,

'Calendar'[Date]

"Dates",

[Value]

FORMAT([Dates],"ddd") = WeekStartsOn

)

VAR WeekNos =

IF(

FORMAT(FiscalFirstDay,"ddd") <> WeekStartsOn,

FilteredTableCount +1 ,

FilteredTableCount

)

RETURN

"Week " & WeekNos

Thanks in advance for your help!!

Re: Creating Customized Fiscal Week Column

Icey — Fri, 11 Dec 2020 02:50:39 GMT

Hi Powereports ,

Try this:

1. Create a 'Fiscal Year' column.

Fiscal Year = VAR FiscalStartMonth = 9 RETURN IF ( MONTH ( 'Calendar'[Date] ) < FiscalStartMonth, "FY" & YEAR ( 'Calendar'[Date] ) - 1, "FY" & YEAR ( 'Calendar'[Date] ) )

2. Create a 'Fiscal WeekNum' column.

Fiscal WeekNum = VAR WeekDay = WEEKDAY ( [Date], 2 ) VAR FiscalStartMonth = 9 VAR FiscalmaxWeek = 52 --Calculation-- VAR FiscalFirstDay = IF ( MONTH ( 'Calendar'[Date] ) < FiscalStartMonth, DATE ( YEAR ( 'Calendar'[Date] ) - 1, FiscalStartMonth, 1 ), DATE ( YEAR ( 'Calendar'[Date] ), FiscalStartMonth, 1 ) ) VAR FilteredTableCount = COUNTROWS ( FILTER ( SELECTCOLUMNS ( GENERATESERIES ( FiscalFirstDay, 'Calendar'[Date] ), "Dates", [Value] ), WEEKDAY ( [Dates], 2 ) = 1 ) ) RETURN FilteredTableCount

3. Create a 'Modified Fiscal Week' column.

Modified Fiscal Week = VAR MaxWeek = CALCULATE ( MAX ( 'Calendar'[Fiscal WeekNum] ), FILTER ( 'Calendar', 'Calendar'[Fiscal Year] = EARLIER ( 'Calendar'[Fiscal Year] ) ) ) VAR DayofMaxWeek = COUNTROWS ( FILTER ( 'Calendar', 'Calendar'[Fiscal Year] = EARLIER ( 'Calendar'[Fiscal Year] ) && 'Calendar'[Fiscal WeekNum] = MaxWeek ) ) RETURN IF ( [Fiscal WeekNum] = BLANK () || [Fiscal WeekNum] = MaxWeek && DayofMaxWeek <> 7, BLANK (), "Week " & [Fiscal WeekNum] )

BTW, .pbix file attached.

Best regards

Icey

If this post helps, then consider Accepting it as the solution to help other members find it faster.

Re: Creating Customized Fiscal Week Column

Powereports — Wed, 16 Dec 2020 20:26:02 GMT

Hi Icey,

Thanks for solution. Your solution did solve my problem to some extent, but i am trying to figure out if there is a way to get 52 weeks as below.

I understand that based on the the Modified Weeknumber logic, Dax gave blank values for Sep1,2020- sep6,2020 but at the same time it returned only 51 weeks for the Fiscal year 2020. So I was wondering if we could use a condition such that if the weeknum <52 and First blank week or last blank week (In this case sep1,2020 -sep6,2020) whichever week has more than 4 days in that week , assign it a weeknumber (In this case Sep01-06 can be week 1?

Thanks again!! Really appreciate your help.

Re: Creating Customized Fiscal Week Column

Icey — Thu, 17 Dec 2020 03:45:18 GMT

Hi Powereports ,

Try this:

Modified Fiscal Week = VAR DaysofBlank = COUNTROWS ( FILTER ( 'Calendar', 'Calendar'[Fiscal Year] = EARLIER ( 'Calendar'[Fiscal Year] ) && 'Calendar'[Fiscal WeekNum] = BLANK () ) ) VAR MaxWeek = CALCULATE ( MAX ( 'Calendar'[Fiscal WeekNum] ), FILTER ( 'Calendar', 'Calendar'[Fiscal Year] = EARLIER ( 'Calendar'[Fiscal Year] ) ) ) VAR DayofMaxWeek = COUNTROWS ( FILTER ( 'Calendar', 'Calendar'[Fiscal Year] = EARLIER ( 'Calendar'[Fiscal Year] ) && 'Calendar'[Fiscal WeekNum] = MaxWeek ) ) VAR Week = IF ( [Fiscal WeekNum] = BLANK () || [Fiscal WeekNum] = MaxWeek && DayofMaxWeek <> 7, BLANK (), [Fiscal WeekNum] ) VAR ModifiedWeek = SWITCH ( TRUE (), MaxWeek >= 53, Week, MaxWeek = 52 && DayofMaxWeek >= 4, [Fiscal WeekNum], MaxWeek <= 52 && DayofMaxWeek < 4 && DaysofBlank >= 4, IF ( [Fiscal WeekNum] <> MaxWeek, Week + 1 ) ) RETURN IF ( ModifiedWeek <> BLANK (), "Week " & ModifiedWeek )

Best regards

Icey

If this post helps, then consider Accepting it as the solution to help other members find it faster.

Re: Creating Customized Fiscal Week Column

Icey — Thu, 24 Dec 2020 03:03:31 GMT

Hi Powereports ,

Can this meet your requirements?

Best Regards,

Icey

Re: Creating Customized Fiscal Week Column

Powereports — Tue, 29 Dec 2020 17:08:06 GMT

Icey ,

Thanks a ton!! Your helped me to achieve the desired output.