Forum Discussion

Saes's avatar
Saes
Icon for Helper I rankHelper I
6 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 y...
Need Help
  • v-xuding-msft's avatar
    v-xuding-msft
    6 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/

     

v-xuding-msft's avatar
v-xuding-msft
Icon for Community Support rankCommunity Support
6 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/