Fiscal Calendar and week number starting from, April?

[Anonymous]

ase can some one help me with the fiuscal calender.

Ive seen the videos about teh dax to use for a July financial year but our company is in teh uk and runs from April to March each finacial year.

The dax comments say just to adjust the numbers to offset  to thh relevant date you need however it creates issues, month number 0 and also has month number 13.  I just want Aril being month Number 1 and March being month number 12.

Any help will be much appreciated thanks 

[Greg_Deckler · Accepted solution]

Here is a potential solution assuming you have some kind of date table with a column called Value that has your dates in it:

Create a "Standard Weeknum" column using WEEKNUM([Value])

Create a "Standard Month" column using MONTH([Value])

Create a "Custom Month" column using:

Custom Month = 
    SWITCH(TRUE(),
        [Standard Month] >= 4,[Standard Month] - 3,
        9 + [Standard Month]
    )

Create a "Custom Weeknum" column using:

Custom Weeknum = 
    VAR __StartingWeekNum = WEEKNUM(DATE(YEAR([Value]),4,1))
    VAR __EndingWeekNum = WEEKNUM(DATE(YEAR([Value]),12,31)) - __StartingWeekNum + 1
    RETURN
    SWITCH(TRUE(),
        [Standard WeekNum] >= __StartingWeekNum,[Standard WeekNum] - __StartingWeekNum + 1,
        __EndingWeekNum + [Standard WeekNum]
    )

See attached, Table4.

[ronkochanowski]

Hi @Greg_Deckler ,

I found this measure to be intriguing when compared to what I thought was a different way of doing the same thing.  And, you're the author of both methods...so...
This is the older way of yours to get to a custom week number (fiscal week number) that I had found:

FiscalWeekNumber =
VAR fw = [WeekNum] - WEEKNUM(DATE(DateDim[Year],9,1),2) + 1
RETURN
IF(fw <= 0, 52 + fw, fw)

When I compare the results between the method describe in this thread to the above I get close, but different results.  Compare the files OLD FiscalWeekNumber and NEW FiscalWeekNumber.  The results for 2017-12-31 through 2018-01-07 display the inconsistency.  The NEW method drops FiscalWeekNumber = 19.  I like the idea of using the SWITCH function instead of an IF and am wondering what's causing the difference?  By the way, each year at 12-31, the inconsistency redisplays.

NEW - FiscalWeekNumber Method.pbix

OLD - FiscalWeekNumber Method.pbix 

[Anonymous]

That worked thank you for your help