Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

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 2
Greg_Deckler
Community Champion
Community Champion

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

 

 



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...
amitchandak
Super User
Super User

@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

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.