Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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

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

DateboolHolidayExcludebool5DayWork
8/2/2001
8/3/2010
8/4/2000

 

 

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

IndividualProjectProjStartProjEndWorkWeek
118/1/208/7/205
128/4/208/5/205
138/7/208/20/205
218/3/208/6/206
228/9/208/15/207
238/12/208/24/205

 

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,

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    5 years ago

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