Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Thanks in advance
Solved! Go to Solution.
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)
Respectfully,
Zoe Douglas (DataZoe)
Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/
You can refer to the calendar in this file. The weekend has been excluded from workday
https://www.dropbox.com/s/y47ah38sr157l7t/Order_delivery_date_diff.pbix?dl=0
Other you can update from holiday table
New column in calendar getting holiday from other table
New Column = maxx(filter(table2,table2[Col1]= table1[col1] && table2[Col2]= table1[col2] ),table2[required_col])
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)
Respectfully,
Zoe Douglas (DataZoe)
Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
84 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |