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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

Filter rows that selected date is between their "start" and "finish" dates

Hi,

I have a table like this:

JobHoursStart DateFinish Date
j1202021.102021.15
j2302021.112021.16
j3152021.142021.18
............

 

(Noticed the dates are "Year.WorkWeek" - decimal number, not dates)

And I have a bar chart for "hours" with its "shared axis" being other "Year.WorkWeek" column (from calendar table).

What I'm trying to achive is - when a user clicks on a bar (selecting a "Year.WorkWeek" value), I want the table visual to show all jobs that the selected date is between their "start" and "finish" dates.

For example -
if the user clicks on "2021.14" - the table will show all 3 jobs.
if the user clicks on "2021.17" - the table will show only j3.

 

I've tried a lot of different options with no luck...

Is it possible?

 

Thank you.

1 ACCEPTED SOLUTION
v-yingjl
Community Support
Community Support

Hi @Anonymous ,

Extract the Year.Week column from the calendar table as a single calculated table to use it as the slicer:

Year.WorkWeek = DISTINCT('Calendar'[Year.WorkWeek])

Create a measure like this, put it in the visual filter and set its value as 1:

visual control = 
IF (
    NOT ( ISFILTERED ( 'Year.WorkWeek'[Year.WorkWeek] ) ),
    1,
    IF (
        SELECTEDVALUE ( 'Year.WorkWeek'[Year.WorkWeek] )
            >= SELECTEDVALUE ( 'Table'[Start Date] )
            && SELECTEDVALUE ( 'Year.WorkWeek'[Year.WorkWeek] )
                <= SELECTEDVALUE ( 'Table'[Finish Date] ),
        1
    )
)

filter.png

 

s1.pngs2.png

 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
v-yingjl
Community Support
Community Support

Hi @Anonymous ,

Extract the Year.Week column from the calendar table as a single calculated table to use it as the slicer:

Year.WorkWeek = DISTINCT('Calendar'[Year.WorkWeek])

Create a measure like this, put it in the visual filter and set its value as 1:

visual control = 
IF (
    NOT ( ISFILTERED ( 'Year.WorkWeek'[Year.WorkWeek] ) ),
    1,
    IF (
        SELECTEDVALUE ( 'Year.WorkWeek'[Year.WorkWeek] )
            >= SELECTEDVALUE ( 'Table'[Start Date] )
            && SELECTEDVALUE ( 'Year.WorkWeek'[Year.WorkWeek] )
                <= SELECTEDVALUE ( 'Table'[Finish Date] ),
        1
    )
)

filter.png

 

s1.pngs2.png

 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

I just saw the notification...sorry for the late response - but it works great! thank you very much!

Fowmy
Super User
Super User

@Anonymous 

It seems you have linked your calendar table to the data table. I am not sure how your model looks like. I would suggest you convert the year.Weeknumber column to a propper date as follows. I did it just for Sart Date, you can repeat the same for the End Date. 

SartDate = 
var __year = int(LEFT(Table1[Start Date],4))
var __week = int(MID(Table1[Start Date],5,2))
 return
DATE(__year,1,-2)-WEEKDAY(DATE(__year,1,3))+__week*7


 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Anonymous
Not applicable

@Fowmy 

I have a proper date columns for both of them...we're using the "Year.WeekNumber" just for convenience.

But I still don't see how it is solves the problem...can you elaborate?

 

Thanks

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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