Filter the To date to automatically update today's date

[Meeresblume]

Hello,

I have set a date filter in an evaluation to select the date from - to.

Now I would like the To date to always be automatically set to today's date. Is this possible?

As an example

Today is October 31st, 2023 so the To date should be October 31st, 2023.

If I call up the evaluation tomorrow, November 1st, 2023 (automatic update every day at 7am), it should say November 1st, 2023 after the update.

Thanks for the help.

[v-junyant-msft]

Hi @Meeresblume ,

You can use DAX to create a new measure to achieve the functions you want.

The following DAX might work for you:

Measure = 
VAR _END = TODAY()
VAR _START = DATE(YEAR(_END),1,1)
RETURN
IF(MAX('Table'[date]) >= _START && MAX('Table'[date]) <= _END ,1,0)

Here I have set the start date as January 1, 2023, which you can change by yourself according to your needs:

VAR _START = DATE(YEAR(_END),1,1)

And here I use this DAX to set the end data as today:

VAR _END = TODAY() 

(My test date is November 2nd, 2023)

Once you've created the measure correctly with DAX, you'll need to select the visual that applies the fliter and place the measure in the fliter. I will help you finish the next step here:

You need to click the visual first:

Then you need to drag the measure into 'Fliters on the visual':

Click the 'is less than' drop-down box and choose 'is':

Fill in the value '1' and apply:

And the final output is shown in the following figure:

Compare with the original data:

Best Regards,

Dino Tao

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

[Meeresblume]

Thank you for your Answer. But this is not what i mean.

I need this in the Data Slicer Filter. Not in de Filter in the Table. Users should still be able to filter the date themselves, hence the data Slicer. But after each update, today's date should be written or updated.

Sorry my English is not so good.

[v-junyant-msft]

Hi @Meeresblume ,

Sorry I didn't understand what you meant before. Next, I'll give you a way to create a slicer to achieve your goals.

You can create a new table by using the DAX following:

Table 2 = 
CALENDAR(
    DATE(2021,1,1),TODAY())

This function is to create a date table, the start date of the date table can be customized, and the end date is always the date of the current day ( for example, today is 2023.11.2, then the date table will be 2023.11.2).

And here, you can set the start date to the earliest date in the table you want to filter on, or you can set it to a date earlier than that date. You just need to make sure that you include all the dates in the table you want to filter in that table:

Then you need to create a relationship between the date column in the table you just created and the date column in the table you want to filter, which you can do in the Model view in Power BI Desktop:

Next, you can create a slicer and drag the table you just created into the slicer:

The final result is shown in the following figure:

I filtered the data for the period from 2023.4.22 to today, and it shows correctly.

Best Regards,

Dino Tao

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