Forum Discussion

Preetsingh86's avatar
Preetsingh86
Regular Visitor
1 year ago
Solved

Calculate Week Number

Hi, I am trying to calculate financial week number for the dates column. Our financial year starts from Sept - Aug, and our first day of work is Sunday. I have tried DAX measure WEEKNUM but not sure what returntype will be and then I presume I have to add additional formula to ensure my week starts from sunday

 

Any help will be great

 

Thank You 

  • BeaBF's avatar
    BeaBF
    1 year ago

    Preetsingh86 Hi! Try to adapt this new calculated column to your data:

     

    Financial Year =
    IF(
    MONTH([Date]) >= 9,
    YEAR([Date]),
    YEAR([Date]) - 1
    )

    Financial Week Number =
    VAR StartOfYear =
    DATE([Financial Year], 9, 1)

    VAR WeekNumber =
    WEEKNUM([Date] - StartOfYear + 1, 11)
    RETURN
    WeekNumber

     

    BBF

2 Replies

  • BeaBF's avatar
    BeaBF
    Super User
    1 year ago

    Preetsingh86 Hi! Try to adapt this new calculated column to your data:

     

    Financial Year =
    IF(
    MONTH([Date]) >= 9,
    YEAR([Date]),
    YEAR([Date]) - 1
    )

    Financial Week Number =
    VAR StartOfYear =
    DATE([Financial Year], 9, 1)

    VAR WeekNumber =
    WEEKNUM([Date] - StartOfYear + 1, 11)
    RETURN
    WeekNumber

     

    BBF