Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
According to the image below, the year 2022 starts on Jan 31, 2021. But our requirement is to start the year on the first tuesday of the year.
I need another column, which shows the weeknumbers. Can anyone please help.
For ex: Jan 31, 2021 is week 52, 2021
Feb 1, 2021 is week 52, 2021
Feb 2, 2021 is week 1, 2022
Feb 3, 2021 is week 1, 2022
Feb 4, 2021 is week 1, 2022
Feb 5, 2021 is week 1, 2022
Feb 6, 2021 is week 1, 2022
Feb 7, 2021 is week 1, 2022
Feb 8, 2021 is week 1, 2022
Feb 9, 2021 is week 2, 2022
Solved! Go to Solution.
Hi @snemilipuri
Create two calculated columns as:
FISCAL_YEAR =
IF(
'Table'[FISCAL_DATE]<MINX(FILTER('Table',WEEKDAY('Table'[FISCAL_DATE])=2),'Table'[FISCAL_DATE]),
2021,
2022
)
Weeknum =
var _fiscal_start=MINX(FILTER('Table',WEEKDAY('Table'[FISCAL_DATE])=2),'Table'[FISCAL_DATE])
var _weeknum=
IF(
'Table'[FISCAL_DATE]>=_fiscal_start,
INT(('Table'[FISCAL_DATE]-_fiscal_start)/7)+1,
51-INT(('Table'[FISCAL_DATE]-_fiscal_start)/7)
)
return
CONCATENATE(CONCATENATE(CONCATENATE("week",_weeknum),","),'Table'[FISCAL_YEAR])
Here is the output:
The pbix is attached, please try it.
If you still have some question, please don't hesitate to let me known.
Best Regards,
Link
Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!
Hi @snemilipuri
Create two calculated columns as:
FISCAL_YEAR =
IF(
'Table'[FISCAL_DATE]<MINX(FILTER('Table',WEEKDAY('Table'[FISCAL_DATE])=2),'Table'[FISCAL_DATE]),
2021,
2022
)
Weeknum =
var _fiscal_start=MINX(FILTER('Table',WEEKDAY('Table'[FISCAL_DATE])=2),'Table'[FISCAL_DATE])
var _weeknum=
IF(
'Table'[FISCAL_DATE]>=_fiscal_start,
INT(('Table'[FISCAL_DATE]-_fiscal_start)/7)+1,
51-INT(('Table'[FISCAL_DATE]-_fiscal_start)/7)
)
return
CONCATENATE(CONCATENATE(CONCATENATE("week",_weeknum),","),'Table'[FISCAL_YEAR])
Here is the output:
The pbix is attached, please try it.
If you still have some question, please don't hesitate to let me known.
Best Regards,
Link
Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!
User | Count |
---|---|
94 | |
90 | |
79 | |
77 | |
71 |
User | Count |
---|---|
118 | |
106 | |
91 | |
64 | |
63 |