Creating Customized Fiscal Week Column

[Powereports]

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!!

[Icey · Accepted solution]

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.

[Powereports]

@Icey ,

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

[Icey · Accepted solution]

Hi @Powereports ,

Can this meet your requirements?

Best Regards,

Icey

[Icey]

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.

[Powereports]

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.

[Icey · Accepted solution]

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.