Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hello,
I need help creating a Date table in Power BI using DAX. The table should include columns for day week month and year formats. Additionally, I need to assign week numbers based on the following criteria:
Could someone provide a DAX formula or guidance to implement this logic in a calculated table?
Thanks in advance for your help!
Thanks for the reply from Bibiano_Geraldo and rajendraongole1 , please allow me to provide another insight:
Hi @mehul_sharma77 ,
Here are the steps you can follow:
1. Create calculated table.
Table_flag =
var _table=
CALENDAR(
DATE(2023,1,1),
DATE(2024,12,31))
var _addtable=
ADDCOLUMNS( _table,"Year",YEAR([Date]),"Month",MONTH([Date]),"Week_Test",WEEKNUM([Date],2),"day",WEEKDAY([Date],2))
var _step1_3=
FILTER(
_addtable,[day]>=1&&[day]<=5)
var _weektable=
ADDCOLUMNS(
_step1_3,"week",RANKX(FILTER(_step1_3,[Year]=EARLIER([Year])&&[Month]=EARLIER([Month])),[Week_Test],,ASC,Dense))
return
SUMMARIZE(
_weektable,
[Date],[Year],[Month],[week],[day])
2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi, @mehul_sharma77 ,
First of all, create a new calculated Table and paste this DAX:
DateTable =
ADDCOLUMNS(
FILTER(
CALENDAR(DATE(2023, 1, 1), DATE(2023, 12, 31)), -- Adjust start and end dates
WEEKDAY([Date], 2) <= 5 -- Exclude Saturdays (6) and Sundays (7)
),
"Day Name", FORMAT([Date], "dddd")
)
We can now add the week numbers without worrying about weekends since they are excluded entirely by this DAX:
Week Number =
VAR CurrentMonth = MONTH([Date])
VAR FirstDateOfMonth =
CALCULATE(
MIN([Date]),
FILTER(
ALL(DateTable),
MONTH([Date]) = CurrentMonth
)
)
VAR DaysSinceFirstDate =
DATEDIFF(FirstDateOfMonth, [Date], DAY)
VAR WeekNum = INT(DaysSinceFirstDate / 5) + 1
RETURN WeekNum
At this point, your table should look like this, as you can see in the filter, sunday's and saturday's are not included:
I hope this helps! 🎯
If you found this answer helpful:
✔️ Mark it as the solution to help others find it faster.
👍 Give it a kudo to show your appreciation!
Hi @mehul_sharma77 - you can following DAX formula to create a Date table. It includes the logic for your custom week numbering and day/week/month/year formats.
use calculated table:
Proud to be a Super User! | |
Hi @mehul_sharma77 - I hope it any of the above solutions works, please check and share your views
Proud to be a Super User! | |