Forum Discussion

Saes's avatar
Saes
Helper I
5 years ago
Solved

Fiscal Weeks

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 )

       

        )

    )

Need Help
  • v-xuding-msft's avatar
    v-xuding-msft
    5 years ago

    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/

     

2 Replies

  • v-xuding-msft's avatar
    v-xuding-msft
    Community Support
    5 years ago

    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/