Date Slicer Min Value from another Date Slicer Max Value

[jayt93]

Hi all,

It seems fairly simple in theory but I can't figure out a way to get this to work.

I have one date slicer (Year) and another one (Date).

The two slicers are derived from two different calendar tables.

If I selected 2019 in the Year slicer, I would like the Date slicer to only show dates in 2019 and after.

I'm not sure if this would be possible.

Any input would be greatly appreciated!

Thanks!

[v-alq-msft]

Hi, @jayt93 

As is suggested by @TomMartens , you may manage to do it with visual level filter. You need to use 'list' or 'drop down' slicer type. I created data to reproduce your scenario.

Date Table:

Date Table = CALENDAR(DATE(2017,1,1),DATE(2020,12,31))

Year Table:

Year Table = CALENDAR(DATE(2018,1,1),DATE(2019,12,31))

Then you may create a measure as follows.

IsDisplay = 
var _yeardate = SELECTEDVALUE('Year Table'[Year].[Year])
return
IF(
     YEAR(SELECTEDVALUE('Date Table'[Date]))>=_yeardate,
     1,0
)

Result:

Best Regards

Allan

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

[TomMartens · Accepted solution]

Hi @jayt93 ,

unfortunately, this won't work as you expect.

I created a measure like this:

showDate = 
var SelectedYearCheck = YEAR(CALCULATE(MIN('Date Invoice'[Date])))
var currentyear = YEAR(CALCULATE(MAX('Date Delivery'[Date])))
return
IF(currentyear >= SelectedYearCheck , 1 , 0)

I use this measure to control the items that are displayed in the segmentation of the 2a date like this:

The measure will be honored if the list option is used for the 2a date slicer.

The measure will not be respected by the visualizations between, before or after.

You can create an idea in ideas.powerbi.com or mark this behavior as a problem.

Best regards

Tom

[jayt93]

Ah that's unfortunate!

Definitely hope that they would implement that in the future.

I appreciate the alternative solution though!

[amitchandak]

If you want to control one from another then take the year from the same table from where you are taking a date. What is the advantage of two tables?

[jayt93]

The first slicer dates (year) are derived from invoice dates from a query. The second slicer dates are derived from payment dates from the same query. As a result, the date tables for each slicer would have different ranges. The first slicer is used to set the year-end accounts receivable Aging table while the second slicer is used to set the max payment dates shown on the Aging table. I just want to prevent the user from selecting a max payment date prior to the year-end. Hope that clarifies things.