Forum Discussion

RitaMunha's avatar
RitaMunha
Helper I
6 years ago
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?  ...
Need Help
  • DataZoe's avatar
    DataZoe
    6 years ago

    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)