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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
JLambs20
Helper III
Helper III

Count of Items within a specified date range

Hello,

 

Recently I got help from some great community members with a measure that shows all data points inside of a selected date range (but not using a start or end date in the filter visual).  That was successful but I'd like to take it one step further.  I would like to be able to count the items that show up within a specified range (whether that be by year, quarter, month, or day).  For example, I have selected Q3 in my filter and this is returned in my Gantt chart:

 

Q3 Only.jpg 

As we can see, there are 5 items that "exist" in Q3 (even though part of the tasks exist outside of the date range) so I would like to have a matrix that could show that number.  Here is the code that was used in my previous post:

 

Is Task Visible=
VAR startDate =
     SELECTEDVALUE ( 'SD Time Off'[Time Off Start Date] )
VAR endDate =
     SELECTEDVALUE ( 'SD Time Off'[Time Off End Date] )
RETURN
IF (
       startDate <= MAX ( 'All Dates'[fulldate] )
             && (
                      ISBLANK ( endDate )
                             || endDate >= MIN ( 'All Dates'[fulldate] )
                ),
       1,
       0
   )

 

This formula was used on the gantt in this way: 

JLambs20_0-1649372402627.png

 

I then use a date field from a date table in my filter to select the desired dates. 

 

That all being said, how do I write a measure to be able to count the items? Any assistance would be greatly appreciated!

 

@johnt75 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@JLambs20 , Try new measure like

 

countx(Filter('SD Time Off', 'SD Time Off'[Time Off Start Date] <= MAX ( 'All Dates'[fulldate] ) && (
ISBLANK ( 'SD Time Off'[Time Off End Date] )
|| 'SD Time Off'[Time Off End Date] >= MIN ( 'All Dates'[fulldate] ))
), 'SD Time Off'[Time Off Start Date])

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@JLambs20 , Try new measure like

 

countx(Filter('SD Time Off', 'SD Time Off'[Time Off Start Date] <= MAX ( 'All Dates'[fulldate] ) && (
ISBLANK ( 'SD Time Off'[Time Off End Date] )
|| 'SD Time Off'[Time Off End Date] >= MIN ( 'All Dates'[fulldate] ))
), 'SD Time Off'[Time Off Start Date])

Amazing! Thank you so much! This is going to help out a ton!

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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