topic Re: Fiscal Calendar and week number starting from, April? in DAX Commands and Tips

Fiscal Calendar and week number starting from, April?

Anonymous — Mon, 09 Dec 2019 16:01:01 GMT

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

Re: Fiscal Calendar and week number starting from, April?

Greg_Deckler — Mon, 09 Dec 2019 16:21:11 GMT

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.

Re: Fiscal Calendar and week number starting from, April?

Anonymous — Tue, 10 Dec 2019 10:09:37 GMT

That worked thank you for your help

Re: Fiscal Calendar and week number starting from, April?

ronkochanowski — Wed, 01 Apr 2020 16:18:13 GMT

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