Home › Forums › Power Pivot › Fiscal Year Week Number

Tagged: ,

This topic contains 2 replies, has 2 voices, and was last updated by  Deepak 7 years, 10 months ago.

Viewing 3 posts - 1 through 3 (of 3 total)
  • Author
    Posts
  • June 6, 2016 at 1:01 pm #4843

    Deepak
    Participant
    • Started: 2
    • Replies: 9
    • Total: 11

    Hi

    Would be great if someone can help me with writing a calculated column in dax that gives week number as per the financial year which ends on 31st March?

    Thanks in advance.

    June 6, 2016 at 5:55 pm #4847

    tomallan
    Keymaster
    • Started: 0
    • Replies: 417
    • Total: 417

    Deepak,

    How do you need to handle weeks that straddle March 31: do you want to count them as part of the fiscal year that ended on March 31 or as part of the fiscal year that starts on April 1?

    Tom

    June 6, 2016 at 6:22 pm #4848

    Deepak
    Participant
    • Started: 2
    • Replies: 9
    • Total: 11

    Hi Tom,

    Was able to crack the same by following formula:

    =
    “Week ”
    & INT (
    (
    ‘Calendar'[Date]
    – WEEKDAY ( ‘Calendar'[Date] – 1 )
    – DATE ( YEAR ( ‘Calendar'[Date] ) – ( MONTH ( ‘Calendar'[Date] ) < 4 ), 4, 1 )
    )
    / 7
    )
    + 2

    The week number starts from 1st of April according to Fiscal Year definition of India.

    -Deepak

  • Author
    Posts
Viewing 3 posts - 1 through 3 (of 3 total)

The forum ‘Power Pivot’ is closed to new topics and replies.