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.
I have two columns
InvCom_Date = Which is a date type column
Workweek = Which has data like '23W34' which means year 2023 and week 34
now i want to calculate the difference in weeks.
Tried
for now i have tried splitting the 'workweek' column and extracting week no. and subtracting from the extracted weeknum of 'InvCom_Date'. But issue is handling the years if 'InvCom_Date' is in 2024 then the weekdifference is wrong. I tried a lot with measures I am not able to figure out how to handle year diffrence while calculating also the difference in data types i giving me issue in finding a solution
heres the sample data and expected output
Solved! Go to Solution.
Hi @Karnik_ ,
I suggest you to create a Calendar table to help calculation.
Calendar =
VAR _STEP1 =
ADDCOLUMNS (
CALENDARAUTO (),
"Year", YEAR ( [Date] ),
"Month", MONTH ( [Date] ),
"WeekStart",
[Date] - WEEKDAY ( [Date], 2 ) + 1,
"WeekYear",
YEAR ( [Date] - WEEKDAY ( [Date], 2 ) + 1 )
)
VAR _STEP2 =
ADDCOLUMNS (
_STEP1,
"ActualWeek",
RANKX (
FILTER ( _STEP1, [WeekYear] = EARLIER ( [WeekYear] ) ),
[WeekStart],
,
ASC,
DENSE
)
)
VAR _STEP3 =
ADDCOLUMNS(_STEP2,"Workweek",RIGHT([WeekYear],2)&"W"&FORMAT([ActualWeek],"00"))
RETURN
_STEP3
Then create a calculated column.
Difference in No.of Weeks =
VAR _START =
CALCULATE (
MAX ( 'Calendar'[WeekStart] ),
FILTER ( 'Calendar', 'Calendar'[Workweek] = EARLIER ( 'Table'[Workweek] ) )
)
VAR _END =
CALCULATE (
MAX ( 'Calendar'[WeekStart] ),
FILTER ( 'Calendar', 'Calendar'[Date] = EARLIER ( 'Table'[InvCom_Date] ) )
)
RETURN
DATEDIFF ( _START, _END, WEEK )
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Karnik_ not fully sure what you are trying to achieve, paste sample data in the table format with the expected output.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Hi please check the post I have uploaded
Hi @Karnik_ ,
I suggest you to create a Calendar table to help calculation.
Calendar =
VAR _STEP1 =
ADDCOLUMNS (
CALENDARAUTO (),
"Year", YEAR ( [Date] ),
"Month", MONTH ( [Date] ),
"WeekStart",
[Date] - WEEKDAY ( [Date], 2 ) + 1,
"WeekYear",
YEAR ( [Date] - WEEKDAY ( [Date], 2 ) + 1 )
)
VAR _STEP2 =
ADDCOLUMNS (
_STEP1,
"ActualWeek",
RANKX (
FILTER ( _STEP1, [WeekYear] = EARLIER ( [WeekYear] ) ),
[WeekStart],
,
ASC,
DENSE
)
)
VAR _STEP3 =
ADDCOLUMNS(_STEP2,"Workweek",RIGHT([WeekYear],2)&"W"&FORMAT([ActualWeek],"00"))
RETURN
_STEP3
Then create a calculated column.
Difference in No.of Weeks =
VAR _START =
CALCULATE (
MAX ( 'Calendar'[WeekStart] ),
FILTER ( 'Calendar', 'Calendar'[Workweek] = EARLIER ( 'Table'[Workweek] ) )
)
VAR _END =
CALCULATE (
MAX ( 'Calendar'[WeekStart] ),
FILTER ( 'Calendar', 'Calendar'[Date] = EARLIER ( 'Table'[InvCom_Date] ) )
)
RETURN
DATEDIFF ( _START, _END, WEEK )
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
77 | |
77 | |
36 | |
30 | |
28 |
User | Count |
---|---|
107 | |
100 | |
55 | |
49 | |
45 |