Restrict start date to 1st date of the month and End date to last date of the month in Date slicer

[Anonymous]

Hi All,

I wanted to create a date slicer where, if i select a month and year, the startdate willl automatically choose 1st date of the month(ex: 01/01/2017), same way for end date(01/31//2017), i want to restrict user to do not enter ant other date values

Thanks

Rajmohan

[vcastello]

Hi @Anonymous

If you place a Slicer Visual, and in the field you put a column that has a type 'Date', you'll see how automatically a start date and an end date appear in it. If you click in either of them, you can select through a 'calendar' the exact date.

If you click on the little 'down arrow' that is in the far right you can control these two calendar dates:, and filter in several ways:

-  Between two dates (default).

-  Before (only one date will appear).

-  After (only one date will appear).
-  List. All the dates will appear as a list.

-  Menu. It will hid all the dates and only display them when it is selected.
-  Relative.  Last or Next ...
        - Week.
        - Month.        

        - Quarter.
        - Year.
        -  .....

Hope That Helps

Vicente

[Anonymous]

Hi Vicente,

Thanks for the reply, as you said i know all these options, but client don't want to allow a user to select any date, they wanted to select month and year then the date should appear as 1st day of the month, same for end date to be End of the month.

Thanks

Rajmohan

[vcastello]

Hi @Anonymous

If you have a calendar table you can ....

1.- Create a slicer and place the year field in it

2.- Create a second slicer and place the month field in it.

3.- Create a couple of measures like ...
              MaxDate = MAX (CalendarTable[DateColumn])
              MinDate = MIN (CalendarTable[DateColumn])

When a user selects both slicers, the measures will calculate automatically the first and last date of the calendar table.

If you want, you can change the ( CalendarTable[DateColumn] ) for (DataTable[DateColumn]). In this case, you'll have the first and last date for which there is data in your DataTable. For example, imagine 01/04/2017 is a holiday, and the first day of april that you have data is 04/04/2017. Then, this date will be the result of the measure MinDate. 

Obviously the same applies to the MaXDate Mesaure

Hope That Hels

Vicente

[Anonymous]

Hi @Anonymous,

What happens when you delete the Day hierarchy out of the slicer. when you select for example january won't it automatically select the first for example ? that you could to first till first of the next one but I am not sure how this works, so perhaps you could test that.

L. Meijdam

[Anonymous]

Hi L. Meijdam

If i delete the Day hierarchy out of the slicer, I can't choosed range between two months like 01/01/2016 to 31/05/2017, i have to select each and every month.

Thanks 

Rajmohan

[Anonymous]

Hi @Anonymous,

In that case I don't know either what @vcastello said earlier seems logical so perhaps you could try that ?

Regards,

L. Meijdam

[Anonymous]

Hi @Anonymous,

If I understood it correct you only want the data from the first or last day of the month to be able to be chosen. Maybe you can try to exclude the dates between through Basic Filtering, but that seems like alot of work if you have alot data. But you could always try this if you can't find anything else.

L. Meijdam

[Anonymous]

Hi L. Meijdam,

Thanks for the reply, actully we have a R script chart, for that we need to select start date and end date range, so in this range start date should be 1st day of the month and end date should be end date of the month then only script load suffiecient data. For example: if i choose january in start date the start date should be 01/01/2017 and if I choose May in enddate then End date should be 31/5/2017 like this, I need the data between this range. 

Thanks 

Rajmohan