Forum Discussion
Solved
Create calendar only with workdays
Hi everyone, I`m trying to create a working day calendar with DAX. Does anyone have a suggestion? Can i exclude days from the calendar function? or can i join more than one intreval of dates? ...
Yes, you can do that, try this:
Working Days Calendar = var cal = ADDCOLUMNS(CALENDAR(date(2020,4,1), date(2020,5,1)),"Working Date",if(WEEKDAY([Date]) in {1,7},blank(),[Date])) var workingcal = SUMMARIZE(filter(cal,isblank([Working Date])=false()),[Working Date]) var cal2 = ADDCOLUMNS(CALENDAR(date(2020,1,1), date(2020,2,1)),"Working Date",if(WEEKDAY([Date]) in {1,7},blank(),[Date])) var workingcal2 = SUMMARIZE(filter(cal2,isblank([Working Date])=false()),[Working Date]) return union(workingcal,workingcal2)You can also filter out specific dates with a switch statement:
Working Days Calendar = var cal = ADDCOLUMNS(CALENDAR(date(2020,4,1), date(2020,5,1)),"Working Date",if(WEEKDAY([Date]) in {1,7},blank(),switch([date],date(2020,4,1),blank(),[Date]))) var workingcal = SUMMARIZE(filter(cal,isblank([Working Date])=false()),[Working Date]) var cal2 = ADDCOLUMNS(CALENDAR(date(2020,1,1), date(2020,2,1)),"Working Date",if(WEEKDAY([Date]) in {1,7},blank(),[Date])) var workingcal2 = SUMMARIZE(filter(cal2,isblank([Working Date])=false()),[Working Date]) return union(workingcal,workingcal2)
Yes, you can do that, try this:
Working Days Calendar =
var cal = ADDCOLUMNS(CALENDAR(date(2020,4,1), date(2020,5,1)),"Working Date",if(WEEKDAY([Date]) in {1,7},blank(),[Date]))
var workingcal = SUMMARIZE(filter(cal,isblank([Working Date])=false()),[Working Date])
var cal2 = ADDCOLUMNS(CALENDAR(date(2020,1,1), date(2020,2,1)),"Working Date",if(WEEKDAY([Date]) in {1,7},blank(),[Date]))
var workingcal2 = SUMMARIZE(filter(cal2,isblank([Working Date])=false()),[Working Date])
return union(workingcal,workingcal2)
You can also filter out specific dates with a switch statement:
Working Days Calendar =
var cal = ADDCOLUMNS(CALENDAR(date(2020,4,1), date(2020,5,1)),"Working Date",if(WEEKDAY([Date]) in {1,7},blank(),switch([date],date(2020,4,1),blank(),[Date])))
var workingcal = SUMMARIZE(filter(cal,isblank([Working Date])=false()),[Working Date])
var cal2 = ADDCOLUMNS(CALENDAR(date(2020,1,1), date(2020,2,1)),"Working Date",if(WEEKDAY([Date]) in {1,7},blank(),[Date]))
var workingcal2 = SUMMARIZE(filter(cal2,isblank([Working Date])=false()),[Working Date])
return union(workingcal,workingcal2)
