adding every monday as a calendar date

[MaliniBaktha]

hi, 

I want to refresh my data every monday, but my data is looking at yearly values. Now I want to create a date slicer inside my dashboard to let the viewers know the data is refreshed every monday so they are seeing data as of every monday. How do I do that when I have my data only on yearly level, I have a calendar table but not sure how I put every monday date . Any suggestion on this ?

[AmiraBedh · Accepted solution]

The title of the question is different from what you described in the body. 

If you are refreshing data every monday, you may need to have that info coming from your datasource.

In other words, you are trying to display the date of the monday for the current week : 

The data is refreshed on Monday = 
TODAY() - WEEKDAY(TODAY(), 2) + 1

[peacetheboy]

Go to the Power Query Editor or the Data View in Power BI, and create a new calculated column in your calendar table. You can name it something like Last Monday of Year

try this formula to calculate

Last Monday of Year = VAR CurrentYear = YEAR(Calendar[Date]) VAR LastDayOfYear = DATE(CurrentYear, 12, 31) VAR DaysToSubtract = WEEKDAY(LastDayOfYear, 2) - 1 RETURN LastDayOfYear - DaysToSubtract

[danextian]

You should still be able to add a new query even if you're using direct query.  Try this in a blank query

 = DateTime.SwitchZone(DateTimeZone.LocalNow(), yourzone as number)
//you can wrap the step above in DateTime.From  if you don't need the timemzone or DateTime.From if you need just the date

Load it to the model and use it in a card as max or min (doesn't matter either way)

If refreshed is done via service, the timezone is UTC so you need to convert it to your own timezone.

[MaliniBaktha]

@danextian is this to get a current date in the visuals because I have already used the current date function but I want the date of the refresh as the date 

[danextian]

I assumed you wanted the refresh date or was it something else? The M code I gave will give you the date of refresh as it updates only upon refresh and not based on today's date. Of course, if today was a Monday and you refreshed today then today would be the same as the refresh date.

[AmiraBedh]

What is your datasource ? Is it an Excel file, a SQL database?

You need to schedule the refresh https://learn.microsoft.com/en-us/power-bi/connect-data/refresh-scheduled-refresh 

[MaliniBaktha]

Hi Amira, I know how to schedule the refresh on my power BI report. I have scheduled the refresh every monday but I want that date to reflect on visuals so the viewers can look at the date of the refresh when viewing the visuals 

[AmiraBedh]

You will find the answer here : https://learn.microsoft.com/en-us/azure/devops/report/powerbi/add-last-refresh-time?view=azure-devop...

[MaliniBaktha]

Hi Amira, 

looks like I dont have the access to the advanced editor, is there any dax formula i could do show every tuesday date. like we weekday function where it turns the number of the weekday , I want to return the date of the week ... 

[AmiraBedh]

What is your datasource ? I think you are using a live connection or a direct query.

[MaliniBaktha]

Yes, I am using a direct query 

[AmiraBedh]

what is your data source ?