Count 'Active Days' by Sub-Records, Using Date Table

[Anonymous]

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,

[Greg_Deckler]

@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))

[amitchandak]

@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