Dynamic Date Slicer Depending on the current day

[ozgeozkaya]

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.

[ozgeozkaya]

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

[tamerj1]

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

[ozgeozkaya]

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.

[tamerj1]

@ozgeozkaya 

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

[ozgeozkaya]

@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.

[tamerj1]

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

[tamerj1]

@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. 

[ozgeozkaya]

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

Thank you in advance.

[tamerj1 · Accepted solution]

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] ) )
)

[ozgeozkaya]

@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.

[tamerj1]

@ozgeozkaya 

Yes today is 25th 😅