cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
efhache
Regular Visitor

Display the result of a table for a period whose table contains a start date and an end date.

I have a table that contains an id, a start date, an end date:

IDStart DateEnd date
1234501/02/202107/02/2021
6789001/02/202107/02/2021
9876508/02/202114/02/2021

 

I would like to filter and display the rows for which the period I select in the filter is included in the space between start date and end date. So, for example If the filter is set on period from 01/02 to 07/02, I'll get the lines for IDs 12345 and 67890 ; if the filter is set in period 08/02 to 14/02, I'll get the line for ID 98765; If the filter is set on a period from 02/02 to 05/02 , I'll get also the lines for IDs 12345 and 67890...

I know that I can use slicer to select a date period, but it will filter only on one date column and not select the corresponding period...

Can you help me to get what I want

1 ACCEPTED SOLUTION
lbendlin
Super User III
Super User III

First create a calendar table that covers your date range. This is a calculated table, or a data source.

 

 

Table 4 = CALENDAR(min('Table (2)'[Start Date]),max('Table (2)'[End date]))

 

Use that table to feed your slicer.

Then create a measure to decide if you want to show a row or not

 

 

Visible := 
var a=calendar(min('Table'[Start Date]),max('Table'[End date]))
var b=CALENDAR(min('Table 4'[Date]),max('Table 4'[Date]))
var c=INTERSECT(a,b)
return if(COUNTROWS(c)>0,1,0)

 

 

and lastly add that measure as a filter to your visual and set it to "Visible is 1"

 

lbendlin_0-1613176251872.pnglbendlin_1-1613176282958.png

 

 

 

View solution in original post

2 REPLIES 2
lbendlin
Super User III
Super User III

First create a calendar table that covers your date range. This is a calculated table, or a data source.

 

 

Table 4 = CALENDAR(min('Table (2)'[Start Date]),max('Table (2)'[End date]))

 

Use that table to feed your slicer.

Then create a measure to decide if you want to show a row or not

 

 

Visible := 
var a=calendar(min('Table'[Start Date]),max('Table'[End date]))
var b=CALENDAR(min('Table 4'[Date]),max('Table 4'[Date]))
var c=INTERSECT(a,b)
return if(COUNTROWS(c)>0,1,0)

 

 

and lastly add that measure as a filter to your visual and set it to "Visible is 1"

 

lbendlin_0-1613176251872.pnglbendlin_1-1613176282958.png

 

 

 

View solution in original post

Wonderful, I hadn't thought about the measures at all. I feel a bit silly.

Many thanks

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors
Top Kudoed Authors