How to use custom date picker to filter in visual

[markyochoa]

For alot of my dashboards I've been using a custom date picker as a way to do relative date calculations, as is best practice. 

So I do have a separate table that has all dates spanning across several years. Up until now it hasn't been a problem.

The problem I have no is, how do I use this to filter a table. e.g.

Year Picker (which is just YEAR(DateSelector[Date]))

Year

2022

2023

Actual Table

Date Created	ColumnA
1/1/2022	X
1/1/2023	Y

Previously I just had the "Date Created" field in the filters pane and had it filtering only 2022. But now I need to be able to toggle between last year and this year, while also using the custom date picker. 

I tried various ways to do this like creating a column:

inSelectedYear = YEAR([Date Created]) == YEAR(MIN(DateSelector[Date]))

And try to have that as a boolean filter for True values but it doesn't work. I'm assuming this is a row level problem? Where it will always be true just by how powerbi handles these types of things?

Any help would be appreciated. 

[Anonymous · Accepted solution]

Hi @markyochoa ,

I created a sample pbix file(see the attachment), please check if that is what you want. And assume that there is no relationship created between the table DateSelector and actual.... 

1. Create a slicer using the field YEAR(DateSelector[Date])

2. Create a measure as below to judge if the year of [Date Created] is equal to the selected year of DateSelector[Date]

Flag = 
VAR _selyears =
    ALLSELECTED ( 'DateSelector'[Year] )
VAR _seldate =
    SELECTEDVALUE ( 'Actual'[Date Created] )
RETURN
    IF ( YEAR ( _seldate ) IN _selyears, 1, 0 )

3. Create a table visual and apply a visual-level filter with the condition(Flag is 1)

If the above one can't help you get the desired result, please provide more raw data in your actual table (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[markyochoa]

Thanks! That worked!