Show only available dates in column in Calender view of slicer

[Anonymous]

Hi All,

I have a Refresh Date column, which i will keep it in a slicer with calender view. In this calender, i dont want to see all the dates of the month, i just want to have only dates that available in the refresh date column of data.

Do we have any possibility to achieve this. Please let me know

[Mattkarim9908]

Try using lookup function.

1. create a calendar based on min and Max values from available date field in your databse.

2. use Lookupvalue function the calendar table with your main table and then exclude the blanks in slicer using filters.

Hope this will help.

[amitchandak]

@Anonymous 

Assume Sales is your main table refresh daily

Calendar =Calendar(Min(Sales[Date]),Max(Sales[Date]))

[lkalawski]

Hi vamshik153,

If I understood correctly, then you have a calendar table that has future values, and you only want to have to the current date (in the fact table).

1. Do you generate a calendar using DAX?

If so, add a condition that the date range is only up to the MAX value in the fact table.

2. Do you download the calendar table directly from the database?
In Power Query, add a filter that will filter to the MAX value from the fact table.

If I misunderstood please send details and an example.

_______________
If I helped, please accept the solution and give kudos! 😀

[Anonymous]

@lkalawski  and @amitchandak ,

i have only one table, which will have report refresh dates (ex-feb,1,5,7,11,13,). when you bring this column to slicer and choose view as calender, by default slicer will show all dates of month. But, i want to show only available dates in column(1,5,7,11,13) in calender view of slicer.

Hope you got my point. feel free to discuss

[v-lionel-msft]

Hi @Anonymous ,

Are you experiencing this problem?

For “Between” option in the slicer, the dates range must be continuous, even if your dates are not continuous, the slicer will automatically complete it for you.

For "List" option in the slicer, the dates are discrete values, you can add filters on the visual to filter the dates.

Best regards,
Lionel Chen

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

[Andrey_Hamburg]

Thanks a lot. I also had a slicer with a date column, and it was completed to continous - even if real data had gaps (between 1900 and 2024).