Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
MaliniBaktha
Helper II
Helper II

adding 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 ?

1 ACCEPTED SOLUTION
AmiraBedh
Most Valuable Professional
Most Valuable Professional

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_0-1698148490194.png

 

 


Proud to be a Power BI Super User !

Microsoft Community : https://docs.microsoft.com/en-us/users/AmiraBedhiafi
Linkedin : https://www.linkedin.com/in/amira-bedhiafi/
StackOverflow : https://stackoverflow.com/users/9517769/amira-bedhiafi
C-Sharp Corner : https://www.c-sharpcorner.com/members/amira-bedhiafi
Power BI Community :https://community.powerbi.com/t5/user/viewprofilepage/user-id/332696

View solution in original post

12 REPLIES 12
AmiraBedh
Most Valuable Professional
Most Valuable Professional

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_0-1698148490194.png

 

 


Proud to be a Power BI Super User !

Microsoft Community : https://docs.microsoft.com/en-us/users/AmiraBedhiafi
Linkedin : https://www.linkedin.com/in/amira-bedhiafi/
StackOverflow : https://stackoverflow.com/users/9517769/amira-bedhiafi
C-Sharp Corner : https://www.c-sharpcorner.com/members/amira-bedhiafi
Power BI Community :https://community.powerbi.com/t5/user/viewprofilepage/user-id/332696
peacetheboy
New Member

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
Super User
Super User

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.










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

@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 

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.










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.
AmiraBedh
Most Valuable Professional
Most Valuable Professional

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 


Proud to be a Power BI Super User !

Microsoft Community : https://docs.microsoft.com/en-us/users/AmiraBedhiafi
Linkedin : https://www.linkedin.com/in/amira-bedhiafi/
StackOverflow : https://stackoverflow.com/users/9517769/amira-bedhiafi
C-Sharp Corner : https://www.c-sharpcorner.com/members/amira-bedhiafi
Power BI Community :https://community.powerbi.com/t5/user/viewprofilepage/user-id/332696

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
Most Valuable Professional
Most Valuable Professional

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


Proud to be a Power BI Super User !

Microsoft Community : https://docs.microsoft.com/en-us/users/AmiraBedhiafi
Linkedin : https://www.linkedin.com/in/amira-bedhiafi/
StackOverflow : https://stackoverflow.com/users/9517769/amira-bedhiafi
C-Sharp Corner : https://www.c-sharpcorner.com/members/amira-bedhiafi
Power BI Community :https://community.powerbi.com/t5/user/viewprofilepage/user-id/332696

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
Most Valuable Professional
Most Valuable Professional

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


Proud to be a Power BI Super User !

Microsoft Community : https://docs.microsoft.com/en-us/users/AmiraBedhiafi
Linkedin : https://www.linkedin.com/in/amira-bedhiafi/
StackOverflow : https://stackoverflow.com/users/9517769/amira-bedhiafi
C-Sharp Corner : https://www.c-sharpcorner.com/members/amira-bedhiafi
Power BI Community :https://community.powerbi.com/t5/user/viewprofilepage/user-id/332696

Yes, I am using a direct query 

AmiraBedh
Most Valuable Professional
Most Valuable Professional

what is your data source ? 


Proud to be a Power BI Super User !

Microsoft Community : https://docs.microsoft.com/en-us/users/AmiraBedhiafi
Linkedin : https://www.linkedin.com/in/amira-bedhiafi/
StackOverflow : https://stackoverflow.com/users/9517769/amira-bedhiafi
C-Sharp Corner : https://www.c-sharpcorner.com/members/amira-bedhiafi
Power BI Community :https://community.powerbi.com/t5/user/viewprofilepage/user-id/332696

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.