Fiscal Weeks

[Saes]

Hello all, I'm hoping someone can help - I need some assistance with a date table I've built using DAX. It includes everything I need, except for fiscal weeks.

I work in sales and our financial years starts in April, but we also report in weeks, with Monday being the start of each sales week. The first Monday of April would be Week 1 and in some years we may have 52-weeks and others we will have 53-weeks.

I've read quite a few posts and watched a few videos, but I cannot accurately calculate fiscal weeks. Below is the DAX code - 'Week Fiscal Number - 9999' is the column I need to populate with just a numeric value.

Can anyone help with the additional coding I need?

Thanks in advance!

Ref - Date Index =

VAR BaseCalendar =

CALENDAR ( DATE ( 2000, 1, 1 ), DATE ( 2050, 12, 31 ) )

RETURN

GENERATE (

BaseCalendar,

VAR BaseDate = [Date]

VAR YearDate = YEAR ( BaseDate )

VAR MonthNumber = MONTH ( BaseDate )

VAR YearMonthNumber = YearDate * 12 + MonthNumber - 1

RETURN ROW (

"Year Calendar", YearDate,

        "Year Calendar Decade", FLOOR(YearDate,10),

        "Year Fiscal", YEAR(EOMONTH(BaseDate,-3)),

        "Month Name Long", FORMAT ( BaseDate, "mmmm" ),

        "Month Name Short", FORMAT ( BaseDate, "mmm" ),

        "Month Name Calendar Number", MonthNumber,

        "Month Name Fiscal Number", MONTH(EOMONTH(BaseDate,-3)),

        "Month Calendar Quarter", QUARTER(BaseDate),

        "Month Fiscal Quarter", SWITCH( TRUE(),

                                    MonthNumber = 1, 4,

                                    MonthNumber = 2, 4,

                                    MonthNumber = 3, 4,

                                    MonthNumber = 4, 1,

                                    MonthNumber = 5, 1,

                                    MonthNumber = 6, 1,

                                    MonthNumber = 7, 2,

                                    MonthNumber = 8, 2,

                                    MonthNumber = 9, 2,

                                    MonthNumber = 10, 3,

                                    MonthNumber = 11, 3,

                                    MonthNumber = 12, 3,

                                    BLANK()),

        "Week Calendar Number", WEEKNUM(BaseDate,2),

        "Week Fiscal Number", "9999",

        "Day Name Long", FORMAT( BaseDate, "dddd" ),

        "Day Name Short", FORMAT( BaseDate, "ddd" ),

        "Day Number of Week", WEEKDAY( BaseDate, 2 )

        )

    )

[v-xuding-msft · Accepted solution]

Hi @Saes ,

Please try this:

Fiscal Week = 
--Inputs--
VAR WeekStartsOn = "Mon"
VAR FiscalStartMonth = 4

--Calculation--
VAR FiscalFirstDay = 
    IF(
        'Ref - Date Index'[Month Name Calendar Number] < FiscalStartMonth,
        DATE(
            'Ref - Date Index'[Year Calendar]-1,
            FiscalStartMonth,
            1
        ),
        DATE(
            'Ref - Date Index'[Year Calendar],
            FiscalStartMonth,
            1
        )
    )
VAR FilteredTableCount = 
    COUNTROWS(
        FILTER(
            SELECTCOLUMNS(
                GENERATESERIES(
                    FiscalFirstDay,
                    'Ref - Date Index'[Date]
                ),
                "Dates",
                [Value]
            ),
        FORMAT([Dates],"ddd") = WeekStartsOn
        )
    )

VAR WeekNos = 
    IF(
        FORMAT(FiscalFirstDay,"ddd") <> WeekStartsOn,
        FilteredTableCount+1,        
        FilteredTableCount
    )
RETURN
WeekNos

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

[amitchandak]

@Saes , I created a video on FY. See if that fit in you example

https://youtu.be/euIC0dgGTNM

The file is attached after signature