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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
MaliniBaktha
Helper I
Helper I

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
Resident Rockstar
Resident Rockstar

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
Resident Rockstar
Resident Rockstar

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
Resident Rockstar
Resident Rockstar

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 

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 ... 

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 

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
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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