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 am attempting to calculate the number of active days either as a calculated DAX column or Measure to solve the following problem;
I have two tables one is a date table with a variety of bools to flag days I want to exclude from my count like below:
--DateTable
Date | boolHolidayExclude | bool5DayWork |
8/2/20 | 0 | 1 |
8/3/20 | 1 | 0 |
8/4/20 | 0 | 0 |
The other table is a record of individuals, projects, project start and end dates, and a denotion of which days to count from the date table(work-weeks) like below:
--DataTable
Individual | Project | ProjStart | ProjEnd | WorkWeek |
1 | 1 | 8/1/20 | 8/7/20 | 5 |
1 | 2 | 8/4/20 | 8/5/20 | 5 |
1 | 3 | 8/7/20 | 8/20/20 | 5 |
2 | 1 | 8/3/20 | 8/6/20 | 6 |
2 | 2 | 8/9/20 | 8/15/20 | 7 |
2 | 3 | 8/12/20 | 8/24/20 | 5 |
The Result I need is the count of active-project days for Individuals. As an example, if WorkWeek is 5 I must only count any day with a bool5DayWork = 1.
The result must also not be the sum of the active days of each project, but the active days for the individual. As an example, for individual 1 in my table above, project 2 is sandwhiched between project 1 and 3, so essentially isn't added to the total as I only want to count the active days for the Individual as a whole.
I am still new/learning DAX, and in the event that there is a function similar to excels workday, I would prefer to avoid it since I need greater control over which days are counted.
Thank you in advance,
@Anonymous - If I am reading this correctly, maybe below. @ me if it doesn't work and I'll actually test it! 🙂
Measure =
VAR __Individual = MAX('Table2'[Individual])
VAR __Table1 = SELECTCOLUMNS(FILTER(ALL('Table1'),[Excludebool5DayWork]=1),"Date",[Date])
VAR __Table2 =
SELECTCOLUMNS(
GENERATE(
FILTER(ALL('Table2'),[Individual]=__Individual),
VAR __Start = [ProjStart]
VAR __End = [ProjEnd]
RETURN
GENERATESERIES(__Start,__End,1)
),
"Date",[Value]
)
RETURN
COUNTROWS(INTERSECT(__Table2,__Table1))
@Anonymous ,Not very clear
Please check the formula on the second page that does it using the calendar workday. The way you wanted
https://www.dropbox.com/s/y47ah38sr157l7t/Order_delivery_date_diff.pbix?dl=0
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
23 | |
8 | |
7 | |
6 | |
6 |
User | Count |
---|---|
28 | |
12 | |
10 | |
10 | |
6 |