Forum Discussion

MH3's avatar
MH3
Icon for Helper V rankHelper V
4 years ago

Custom Fiscal Week Calculation

Hi,

 

I have to calculate fiscal week depending upon the Day Number which is Monday it should be the first day of the week.

I used this formula but here it is using Day Name I want to convert into Day Number becuase this logic will work on any condition if there is a language change it will not affect the formula.

New Winter Week = 

--Inputs--
VAR WeekStartsOn = "Mon"
VAR FiscalStartMonth = 11

--Calculation--
VAR FiscalFirstDay = 
    IF(
        MONTH(Dates[Date]) < FiscalStartMonth,
        DATE(
            YEAR(Dates[Date])-1,
            FiscalStartMonth,
            1
        ),
        DATE(
            YEAR(Dates[Date]),
            FiscalStartMonth,
            1
        )
    )
VAR FilteredTableCount = 
    COUNTROWS(
        FILTER(
            SELECTCOLUMNS(
                GENERATESERIES(
                    FiscalFirstDay,
                    Dates[Date]
                ),
                "Dates",
                [Value]
            ),
        FORMAT([Dates],"ddd") = WeekStartsOn
        )
    )
VAR WeekNos = 
    IF(
        FORMAT(FiscalFirstDay,"ddd") <> WeekStartsOn,
        FilteredTableCount + 1,
        FilteredTableCount
    )
RETURN
   WeekNos


WeekStartsOn should be Monday and it should calculate the weeks accordingly till 53 or 52..

I am kinda unable to change the formula like that and Help? 
amitchandak  

1 Reply

  • I would recommend you use an external calendar table (for example maintained in an Excel file in a Sharepoint)  for anything with fiscal calendars and especially fiscal weeks.