Forum Discussion
Create a calendar with specific dates
Sachy123
I am not sure if I understood your question correctly. If you need to create a date table with only the 7th Working day for each month considering Saturday and Sunday as weekends, then the following code in DAX for a new table should get you what you are after:Table = var __Calendar = ADDCOLUMNS( CALENDAR("01/01/2020","31/12/2020"), "Year" , YEAR([Date]), "Month", MONTH([Date]), "Week", FORMAT([Date],"ddd"), "7th Working Day", IF( WEEKDAY([Date],2) in {6,7}, 0 , 1) ) return FILTER( ADDCOLUMNS( __Calendar, "DateAdded" , SUMX( FILTER(__Calendar, [Year] = EARLIER([Year]) && [Month]=EARLIER([Month]) && [Date] <= EARLIER([Date])), [7th Working Day] ) ), [DateAdded] = 7 )________________________
If my answer was helpful, please click Accept it as the solution to help other members find it useful
Click on the Thumbs-Up icon if you like this reply 🙂
Sachy123
I am not sure if I understood your question correctly. If you need to create a date table with only the 7th Working day for each month considering Saturday and Sunday as weekends, then the following code in DAX for a new table should get you what you are after:
Table =
var __Calendar =
ADDCOLUMNS(
CALENDAR("01/01/2020","31/12/2020"),
"Year" , YEAR([Date]),
"Month", MONTH([Date]),
"Week", FORMAT([Date],"ddd"),
"7th Working Day", IF( WEEKDAY([Date],2) in {6,7}, 0 , 1)
)
return
FILTER(
ADDCOLUMNS(
__Calendar,
"DateAdded" ,
SUMX(
FILTER(__Calendar, [Year] = EARLIER([Year]) && [Month]=EARLIER([Month]) && [Date] <= EARLIER([Date])),
[7th Working Day]
)
),
[DateAdded] = 7
)________________________
If my answer was helpful, please click Accept it as the solution to help other members find it useful
Click on the Thumbs-Up icon if you like this reply 🙂
