Home Forums Power Pivot Fiscal Year Week Number

Tagged: ,

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

Viewing 3 posts - 1 through 3 (of 3 total)
  • Author
    Posts
  • #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.

    #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

    #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

Viewing 3 posts - 1 through 3 (of 3 total)

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