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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
JLambs20
Helper III
Helper III

Show items between start and end dates

Hello!

 

I have run into a recurring situation where individuals are asking to be able to use a filter to see all items in a table that "exist" between a start and end date, but the date they select is neither a start or end date. 

 

For example, in this chart, we see items 1 and 4 are both ongoing on Jan 23.  So, an end user wants to be able to select Jan 23 and see these two items be returned in the chart.  As we know, if the filter is using the start date as the field and we select, Jan 23, nothing would be returned.  

 

Date Gantt.jpg

 

I'd like to learn how to properly model this so that I can apply the lessons learned to multiple dashboards since this request continues to pop  up more and more.  Do I need a date table? If so, do I build a relationship between that table and my task table? I'm just not sure so that's why I'm reaching out. 

 

Or, if anyone has any good resources about this topic, I'll take that as well!

 

Thanks in advance!

 

1 ACCEPTED SOLUTION

You could swap the MIN('Date'[Date]) for MAX('Date'[Date]), that should do it

View solution in original post

9 REPLIES 9
johnt75
Super User
Super User

You should definitely build a proper date table, that helps with all sorts of things such as time intelligence measures and making sure that all possible dates are available for a user to select from a slicer, not just the dates which appear in your data.

In this case you wouldn't want the date table to be related to your data table. As you said, that would filter just those tasks which started on a particular date.

You can create a measure and use that as a filter on the visual where you want to show tasks.

Is Task Visible =
var startDate = SELECTEDVALUE('Table'[start date])
var endDate = SELECTEDVALUE('Table'[end date])
return IF( startDate <= MIN('Date'[Date]) && 
   ( ISBLANK( endDate) || endDate >= MAX('Date'[Date]) ), 
1, 0 )

This will allow users to select either a specific date or a range of dates. Add it as a filter to only show rows when the value is 1

Thank you for your response! I have gone ahead and create the measure, but now I'm just a bit confused on where I'm applying it and what field I'm using as my date filter. So, you said to place the measure on the visual which I'm assuming you mean in the filter pane? If that's correct, do I need to set it to equal 1 in the filter pane? As for the filter drop down, which field am I using? A date field from the date table? If you had screenshots of how you'd apply this, that would be great.  You don't have to use it on a Gantt chart obviously but seeing it would be a bit easier to see where you're coming from.  I appreciate your response though!

Yes, place the measure on the filter pane like

johnt75_0-1648830088567.png

You would use the Date field from your Date table on the slicer.

For the most part, this works pretty well, although I have discovered some limitations (to be fair, I didn't ask for this in the beginning).  But let's say I want to see everything that is going on in February.  If I have an entry that starts on Feb 10 and ends sometime in March, that entry will not be reflected in my visual since it doesn't occur in all of February.  I'm not sure how to address that (or if it's even possible)? 

Icey
Community Support
Community Support

Hi @JLambs20 ,

 

Do you mean to only show that both the start date and end date are within the selected date range? For example:

Is Task Visible =
VAR startDate =
    SELECTEDVALUE ( 'Table'[start date] )
VAR endDate =
    SELECTEDVALUE ( 'Table'[end date] )
RETURN
    IF (
        startDate >= MIN ( 'Date'[Date] ) -----------------changed
            && (
                ISBLANK ( endDate )
                    || endDate <= MAX ( 'Date'[Date] ) --------------changed
            ),
        1,
        0
    )

TASK.gif

For more details, please check the attached .pbix file.

 

 

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hello Icey!
From what I'm seeing, you are close but it's not quite what I'm after.  If you look at my screenshot here, there are two tasks (green/dark blue) that span a few similar dates.  In this case, they both span Jul 22 to Jul 31.  However, the bottom task (dark blue, kind of obscured) does not start or end within that time range.  

 

SD1.jpg

 

So what I am looking for is if I were to select an individual date, or a particular month, I would like to see all of the tasks that either start, end, or occur in my selection.  But as you can see below when I select just July, nothing appears in the gantt.  

SD2.jpg

If I could select either a year, quarter, month, or day and see all tasks in that date range, regardless of whether they start, end, or occur in that date frame, that's what I'm ultimately after.  

 

So, in the above example, when I select the month of july, I want to be able to see both the green and dark blue tasks in the gantt chart because they both occur in July, even though the dark blue task does not have a start or end in July.  Make sense?

 

Thanks!

Josh

 

You could swap the MIN('Date'[Date]) for MAX('Date'[Date]), that should do it

Hi John,

 

I was wondering if you could help me out with regards to an addition to this measure.  Currently, the default chart shows all values regardless of blanks in the "start" or "end" column.  To be clear, that is fine as I want the default to show those items that have blank start dates (I understand how absurd that sounds considering I'm showint a gantt chart).  However, I'd like the chart to NOT show those items with blanks when a date in the date slicer is selected.  As it sits, the chart will show all of the items that satisfy the date criteria but it's also still showing those with a blank start date. Thank you!

I think you may be on to something here.  So far, it looks like it works but I'm going to QC it some more and if that's the case, I'll accept it as the solution! Thank you so much!

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.