Forum Discussion
Helper IIadding every monday as a calendar date
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 ?
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
12 Replies
- AmiraBedh
Super User
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 - 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
MaliniBakthaHi 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-devops&tabs=private
- 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 dateLoad 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.
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
