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 PBI Experts,
I have requirement to show the week number which it should reset for every month. The week always should start on Monday and end on Sunday. Below is the April month calendar screen shot for your reference. I am using date dimension in my model where i need to add the week number as per below screen shot logic. Please help.
Solved! Go to Solution.
Hi @prakash11440278 ,
I created a sample pbix file(see the attachment), please check if that is what you want.
Column = EOMONTH('Date'[Date],0)+(7-WEEKDAY(EOMONTH('Date'[Date],0),2))
Week =
VAR _minwkdate =
CALCULATE (
MIN ( 'Date'[Date] ),
FILTER (
'Date',
MONTH ( 'Date'[Date] ) = EARLIER ( 'Date'[Date].[MonthNo] )
&& WEEKDAY ( 'Date'[Date], 2 ) = 1
)
)
VAR _minpwkdate =
CALCULATE (
MIN ( 'Date'[Date] ),
FILTER (
'Date',
MONTH ( 'Date'[Date] )
= EARLIER ( 'Date'[Date].[MonthNo] ) - 1
&& WEEKDAY ( 'Date'[Date], 2 ) = 1
)
)
VAR _precol =
CALCULATE (
MAX ( 'Date'[Column] ),
FILTER ( 'Date', 'Date'[Column] < EARLIER ( 'Date'[Column] ) )
)
VAR _maxwkdate =
CALCULATE (
MAX ( 'Date'[Date] ),
FILTER (
'Date',
MONTH ( 'Date'[Date] ) = EARLIER ( 'Date'[Date].[MonthNo] )
&& WEEKDAY ( 'Date'[Date], 2 ) = 7
)
)
VAR _mindate =
IF ( 'Date'[Date] <= _precol, _minpwkdate, _minwkdate )
VAR _maxdate =
IF ( 'Date'[Date] <= _precol, _precol, 'Date'[Column] )
RETURN
IF (
'Date'[Date] >= _mindate,
"W"& ROUNDDOWN ( ( 'Date'[Date] - _mindate ) / 7, 0 ) + 1
)
Best Regards
Hi @Anonymous ! I am applying the same logic to a report I am building and this solution is working for the first year. However, the subsequent years are not calculating correctly. What needs to be added/changed for it to flow across multiple years?
Hi @prakash11440278 ,
I created a sample pbix file(see the attachment), please check if that is what you want.
Column = EOMONTH('Date'[Date],0)+(7-WEEKDAY(EOMONTH('Date'[Date],0),2))
Week =
VAR _minwkdate =
CALCULATE (
MIN ( 'Date'[Date] ),
FILTER (
'Date',
MONTH ( 'Date'[Date] ) = EARLIER ( 'Date'[Date].[MonthNo] )
&& WEEKDAY ( 'Date'[Date], 2 ) = 1
)
)
VAR _minpwkdate =
CALCULATE (
MIN ( 'Date'[Date] ),
FILTER (
'Date',
MONTH ( 'Date'[Date] )
= EARLIER ( 'Date'[Date].[MonthNo] ) - 1
&& WEEKDAY ( 'Date'[Date], 2 ) = 1
)
)
VAR _precol =
CALCULATE (
MAX ( 'Date'[Column] ),
FILTER ( 'Date', 'Date'[Column] < EARLIER ( 'Date'[Column] ) )
)
VAR _maxwkdate =
CALCULATE (
MAX ( 'Date'[Date] ),
FILTER (
'Date',
MONTH ( 'Date'[Date] ) = EARLIER ( 'Date'[Date].[MonthNo] )
&& WEEKDAY ( 'Date'[Date], 2 ) = 7
)
)
VAR _mindate =
IF ( 'Date'[Date] <= _precol, _minpwkdate, _minwkdate )
VAR _maxdate =
IF ( 'Date'[Date] <= _precol, _precol, 'Date'[Column] )
RETURN
IF (
'Date'[Date] >= _mindate,
"W"& ROUNDDOWN ( ( 'Date'[Date] - _mindate ) / 7, 0 ) + 1
)
Best Regards