The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi,
I have been putting together a custom financial year callendar, i have realised that the Weeknum dax is returning the same week number for the range of 9 days, is there a way to fix it?
Example below
Thank you in advance for any advice
Solved! Go to Solution.
@Tajkment How about this?
Column 3 =
VAR __Date = [Date]
VAR __StartDate = DATE( 2024, 7, 1 )
VAR __StartDay = WEEKDAY( __StartDate )
VAR __Result = COUNTROWS( FILTER( 'Calendar', [Date] <= __Date && [Date] >= __StartDate && WEEKDAY([Date]) = __StartDay ) )
RETURN
__Result
@Greg_DecklerYou are 100% right, that is not what i would like,
any advices what should i use instead?
Thank you
@Tajkment That would depend on what exactly you are trying to achieve. How is it supposed to work? When does your financial year start and end?
I am looking to generate an financial year callendar, The financial year runs from July to June, so i would get Financial Year,month, quarter, week, the week is starting on Monday,
The end goal is to use this one as a slicer, so the end users will be able to filter the visualisations by year / quarter / month / week
@Tajkment How about this?
Column 3 =
VAR __Date = [Date]
VAR __StartDate = DATE( 2024, 7, 1 )
VAR __StartDay = WEEKDAY( __StartDate )
VAR __Result = COUNTROWS( FILTER( 'Calendar', [Date] <= __Date && [Date] >= __StartDate && WEEKDAY([Date]) = __StartDay ) )
RETURN
__Result
@Tajkment Don't use EDATE? You are using EDATE with a -6. This means that August 29th through 31st will all return a date of February 29th, which is 6 months back from August. Since February doesn't have a 30th or 31st, those will both return 29th of February or last date in February. So, probably not what you want.
User | Count |
---|---|
11 | |
8 | |
6 | |
6 | |
5 |
User | Count |
---|---|
24 | |
14 | |
13 | |
8 | |
8 |