The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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!
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
141 | |
109 | |
108 | |
76 | |
63 |
User | Count |
---|---|
273 | |
129 | |
123 | |
101 | |
91 |