topic Re: Dynamic Date Slicer Depending on the current day in DAX Commands and Tips

Dynamic Date Slicer Depending on the current day

ozgeozkaya — Mon, 17 Apr 2023 11:03:01 GMT

Hi everyone,

I have a date slicer for selecting From and To dates from the Calendar Table. The data shows accordingly.

I want to set the From and To dates on the slicer dynamically according to the current day. From date should start from 25th of the month and To date should end with 24th of the following month.

Criteria: If the current day equals or after the 25th of the current month then From date should be set to 25th of that month and To date to be set to 24th of the following month.

Examples:
Let's say today is 17th of April. Then the From date will be 25.02.2023 and To date will be 24.03.2023

Let's say today is 25th of April. Then the From date will be 25.03.2023 and To date will be 24.04.2023

Let's say today is 1st of May. Then the From date will be 25.03.2023 and To date will be 24.04.2023

I hope any some can help me with how to set the From and To dates on the slider dynamically.

Re: Dynamic Date Slicer Depending on the current day

tamerj1 — Tue, 18 Apr 2023 09:48:03 GMT

Hi ozgeozkaya
Please refer to attached sample fie. Create the following filter measure, place in the filter pane of the slicer, select "is not blank" then apply the filter.

DateFilter = VAR CurrentDay = DAY ( TODAY ( ) ) VAR MonthOffset = IF ( CurrentDay < 25, - 1 ) VAR StartEOMonth = EOMONTH ( TODAY ( ), MonthOffset - 1 ) VAR EndEOMonth = EOMONTH ( TODAY ( ), MonthOffset ) VAR StartMonth = MONTH ( StartEOMonth ) VAR EndMonth = MONTH ( EndEOMonth ) VAR StartYear = YEAR ( StartEOMonth ) VAR EndYear = YEAR ( EndEOMonth ) VAR StartDate = DATE ( StartYear, StartMonth, 25 ) VAR EndDate = DATE ( EndYear, EndMonth, 24 ) VAR Result = COUNTROWS ( FILTER ( 'Date', 'Date'[Date] >= StartDate && 'Date'[Date] <= EndDate ) ) RETURN Result

Re: Dynamic Date Slicer Depending on the current day

ozgeozkaya — Tue, 18 Apr 2023 05:30:49 GMT

Dear tamerj1 Thank you for the prompt reply. It is working with one glitch. According to the today's date criteria, the slicer should be 25.02.2023 - 24.03.2023 not 25.03.2023 - 24.04.2023 because today is not yet 25th of the month. Once today is 25th or greater than 25th of the current month (April), then it will display as 25.03.2023 - 24.04.2023.

Examples:
Let's say today is 18th of April. Then the From date will be 25.02.2023 and To date will be 24.03.2023

Let's say today is 25th of April. Then the From date will be 25.03.2023 and To date will be 24.04.2023

Let's say today is 1st of May. Then the From date will be 25.03.2023 and To date will be 24.04.2023

Re: Dynamic Date Slicer Depending on the current day

tamerj1 — Tue, 18 Apr 2023 09:48:56 GMT

ozgeozkaya
Yes that was a mistake. Please refer to the updated solution in my original reply.

Re: Dynamic Date Slicer Depending on the current day

ozgeozkaya — Tue, 18 Apr 2023 11:42:15 GMT

Dear tamerj1, the date slicer shows the correct dates now however for some reason it is not filtering the data. I am not sure if this is a bug or not but when you manually modify the slicer and choose the dates it filters the data accordingly but when you leave it as it is in the slicer filter you created, it is not filtering the data. It shows all available data in the table.

Re: Dynamic Date Slicer Depending on the current day

tamerj1 — Tue, 18 Apr 2023 13:25:18 GMT

ozgeozkaya

This is a visual level filter. You need to add it to every visual that needx to be filtered this way.

Re: Dynamic Date Slicer Depending on the current day

ozgeozkaya — Wed, 19 Apr 2023 05:57:59 GMT

tamerj1 Thank you for your reply. I reattached your file. As you may see only the table visual which has date on it is filtered but the other visuals (Card and Column chart) are not able to filter the data correctly after the visual level filter is applied.

Re: Dynamic Date Slicer Depending on the current day

tamerj1 — Wed, 19 Apr 2023 08:03:08 GMT

ozgeozkaya

Yes this method does not work with card visuals. The chart seems to be filtered but not to the desired values. I'll let you know if I was able to find a workaround for that. Very busy today so probably won't reply to you soon meanwhile please share the sample file with visuals via wetransfer or dropbox link.

Re: Dynamic Date Slicer Depending on the current day

ozgeozkaya — Wed, 19 Apr 2023 08:41:37 GMT

tamerj1 here is the link: https://we.tl/t-UUvsmkhE1y

Thank you in advance.

Re: Dynamic Date Slicer Depending on the current day

tamerj1 — Wed, 19 Apr 2023 12:14:20 GMT

Hi ozgeozkaya
Please refer to attached sample file amended as required.

Use the following measure in the three visuals

Sum of Transaction Period = SUMX ( FILTER ( VALUES ( 'Date'[Date] ), VAR CurrentDay = DAY ( TODAY ( ) ) VAR MonthOffset = IF ( CurrentDay < 25, - 1 ) VAR StartEOMonth = EOMONTH ( TODAY ( ), MonthOffset - 1 ) VAR EndEOMonth = EOMONTH ( TODAY ( ), MonthOffset ) VAR StartMonth = MONTH ( StartEOMonth ) VAR EndMonth = MONTH ( EndEOMonth ) VAR StartYear = YEAR ( StartEOMonth ) VAR EndYear = YEAR ( EndEOMonth ) VAR StartDate = DATE ( StartYear, StartMonth, 25 ) VAR EndDate = DATE ( EndYear, EndMonth, 24 ) RETURN 'Date'[Date] >= StartDate && 'Date'[Date] <= EndDate ), CALCULATE ( SUM ( Overtime[Transaction Period] ) ) )

Re: Dynamic Date Slicer Depending on the current day

ozgeozkaya — Wed, 19 Apr 2023 12:49:59 GMT

tamerj1 wonderful solution. I will keep an eye on it on 25.04.2023 for final confirmation if you don't mind. Thank you again.

Re: Dynamic Date Slicer Depending on the current day

tamerj1 — Tue, 25 Apr 2023 10:27:07 GMT

ozgeozkaya

Yes today is 25th 😅