Card visual not displaying TODAY() in Power BI service

[mariahMC]

I have a card visual displaying a dynamic date range based on a volume metric selection (i.e. YTD, Trailing 4, etc. ) The end date for the current year does not work when I have a scheduled refresh - if I do a manual refresh it works. The underlying data itself updates as intended, but not the card visual. I think it has something to do with the TODAY() function in my measure. Essentially, I want it to display the last day of the current week when the scheduled refresh occurs. I currently have it setup to refresh on Saturdays at 9pm local time, and want it to display Sunday's date (last day of the week). 

Card Visual:

Measure:

YTD Date Range =

VAR endweek = WEEKNUM(TODAY())

VAR startweek = MIN('Calendar Table'[Week Number])

VAR selectedyear = SELECTEDVALUE('Calendar Table'[Adjusted Year])

VAR startdate =

    CALCULATE(

        MIN('Calendar Table'[Date]),FILTER('Calendar Table','Calendar Table'[Week Number] = startweek))

VAR enddate =

    CALCULATE(

        MAX('Calendar Table'[Date]), FILTER('Calendar Table','Calendar Table'[Week Number] = endweek && 'Calendar Table'[Adjusted Year] = selectedyear))

RETURN

startdate &" - " & enddate

[v-venuppu]

Hi @mariahMC ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @RossEdwards @GeraldGEmerick for the prompt response.

I wanted to check if you had the opportunity to review the information provided and resolve the issue..?Please let us know if you need any further assistance.We are happy to help.

Thank you.

[RossEdwards · Accepted solution]

I would not take this approach.  The TODAY function is relative to the date in the real world.  If your date range is based around your refresh, you should calculate it as part of your refresh.

You can use the function DateTime.UTCNow() to get the date/time when your refresh occurs.  If you want it to be local time, just add/subtract #duration(X,0,0,0) where you place in your hours from UTC.  I'm in Australia so I use "+ #duration(10,0,0,0)".

You can convert this value to a table and store the value as a date.  You could add extra columns to store the start and end of week dates using the functions Date.StartOfWeek() and Date.EndOfWeek().  Load those tables into your model and have your report simply show those pre-calculated dates.  Now your dates will always match your refresh, including if it fails (showing you that its still the old data).

Date.StartOfWeek - PowerQuery M | Microsoft Learn

Date.EndOfWeek - PowerQuery M | Microsoft Learn

[mariahMC]

Thank you this worked for what I needed

[GeraldGEmerick]

@mariahMC Could this possibly be related to an automatic personal bookmark in the service?

[mariahMC]

 I checked my personal bookmarks and there wasn't anything added. Thank you for your response!