Date Calendar with only end of Month Date

[Saniat]

Hi,

In my data, I only have end of month dates e.g. 28 Feb, 31 Mar, and no other dates like 15 Jun, 22 Aug.

However, when I bring that date as a slicer, the slicer shows all the dates within max and min dates.

How can I only show the available dates in the dataset e.g. only end of the month dates ?

Thanks.

[v-chenwuz-msft]

Hi @Saniat ,

No, the default slicer of calendar always contains the date between the max date and min date.

You can try Timeline and show only year and month level. So that the end user can not see the day level.

Best Regards

Community Support Team _ chenwu zhu

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

[PaulDBrown]

It is recommended to use a Date table as a dimension whenever dates are involved. You could then filter the slicer using a measure:

Filter dates = COUNTROWS (RELATEDTABLE(Fact Table))

add the measure to the filter pane for the slicer and set the value to greater or equal to 1

As an alternative, you can create a dimension table for the dates you have using

Dates in fact table = DISTINCT(Fact table[Date])

and then create a relationship between this new table and the fact table, and use the date in the Dates table as the slicer

[Saniat]

I tried that. On the Date table, although I only have end of month dates available, when brought in as a slicer, it shows all the dates of the month.

[Saniat]

I even created a separate table with manual end of month entries, and used it as a slicer, but unfortunately the slicer still shows days during the month.

[PaulDBrown]

Can you post a screenshot please?

[Saniat]

Hi @PaulDBrown 

I have created a separate table for end of months:

... which has been linked with the main table '1'.

When I bring in the Date Slider as a list, I see months and years only:

But when I change this to a 'between' slicer, I can go to a mid-month date (which I do not want):