Limit date picker (date slicer) to only dates available in my dataset

Power BI
1

Hi There, is there a way to prevent user selecting dates where no data available ?

See below pic, I have April 2019 data in my dataset, however no data for Sep 2019. How can I prevent the date slicer to display date picker > April 2019 ?

image
image.png791Ă—497 25.4 KB

2

@akiko_lim,

One way to handle this is to create an Active Dates column in your date table, dynamically tied to the min and max dates in your fact table, and then use this column for your date slicer.

Active Dates = 

VAR MaxDate = MAX( Sales[Purchase Date] )
VAR MinDate = MIN( Sales[Purchase Date] )

RETURN
SWITCH( TRUE(),
    Dates[Date] < MinDate, BLANK(),
    Dates[Date] > MaxDate, BLANK(),
    Dates[Date]
)

image
image.png835Ă—440 9.92 KB

Hope this is helpful. Full solution file posted below.

1 Like
3

With Calender Visual you cannot limit the dates being displayed. you can change the visual to date slicer or drop-down and then apply filter of min and max dates from the table in the visual.

4

@akiko_limm,

@baijumohan raises a good point. While the Active Dates approach above will prevent someone from moving the slider outside the date range of your fact table, it won’t prevent the user from manually entering a date outside that range. However, if they do that, the measure will return a blank for the improper dates entered, and still should give you the correct result.

  • Brian
1 Like
5

Thanks Brian & Baijumohan. You guys are awesome ! :+1: I will provide the “active dates” option as an alternative.

1 Like