Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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.
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:
That worked thank you for your help
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
22 | |
12 | |
8 |
User | Count |
---|---|
76 | |
50 | |
46 | |
16 | |
12 |