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

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors