Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 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...
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
6 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))