cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
joshcorti11
Helper III
Helper III

Automatically Updating a Date Range to Reflect New Report Data

Hi Everyone,

 

I am building a report in power bi with data from salesforce reports. The power bi report gives our managers a one-week overview of sales performance. I am wondering how to set a date range in power bi that automatically updates as the data in the report changes. For example, in the attached image I have manually written in the date which the report covers, but I am wondering if there is a way to make this date automatically change when the data updates.

 

Cheers! 

 

Business Report.PNG

1 ACCEPTED SOLUTION

Hi @joshcorti11 ,

 

Try the following measures:

 

Current Week = 
var Start_date = TODAY() + -1 * WEEKDAY(TODAY(),2) + 1
var End_date = TODAY() + -1 * WEEKDAY(TODAY(),2) + 5
return "Business Report " & Start_date & "-" & End_date
Last Week = 
var Start_date = TODAY() + -1 * WEEKDAY(TODAY(),2) -6
var End_date = TODAY() + -1 * WEEKDAY(TODAY(),2) - 2
return "Business Report " & Start_date & "-" & End_date

image.png

 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
amitchandak
Super User IV
Super User IV

@joshcorti11 , You can not set a range

You can week start , week end and week type in date table

Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)

 

Week Type = Switch( True(),
[start week]<=Today() && [end date]>=Today(),"This Week" ,
[start week]<=Today()-7 && [end date]>=Today()-7,"Last Week" ,
[Week Name]
)

 

you can use week type and save it on this week

 

You can show date in card

measure =
var _min = minx(allselected('Date'), 'Date'[Date])
var _max = maxx(allselected('Date'), 'Date'[Date])

return

_min & " to " & _max



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Hi,

 

thank you for helping with this!

 

will I be putting this code in as a measure?

 

Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)

 

Week Type = Switch( True(),
[start week]<=Today() && [end date]>=Today(),"This Week" ,
[start week]<=Today()-7 && [end date]>=Today()-7,"Last Week" ,
[Week Name]
)

 

I am also wondering what I am supposed to replace if the date was going to reflect last week, (6-7-2021 through 6-11-2021 

 

I see that the second piece of code is a measure

 

measure =
var _min = minx(allselected('Date'), 'Date'[Date])
var _max = maxx(allselected('Date'), 'Date'[Date])

return

_min & " to " & _max

 

If I wanted my date range to show up for 6/7/2021 through 6/11/2021, what would I need to replace in this measure?

Hi @joshcorti11 ,

 

Try the following measures:

 

Current Week = 
var Start_date = TODAY() + -1 * WEEKDAY(TODAY(),2) + 1
var End_date = TODAY() + -1 * WEEKDAY(TODAY(),2) + 5
return "Business Report " & Start_date & "-" & End_date
Last Week = 
var Start_date = TODAY() + -1 * WEEKDAY(TODAY(),2) -6
var End_date = TODAY() + -1 * WEEKDAY(TODAY(),2) - 2
return "Business Report " & Start_date & "-" & End_date

image.png

 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Hi,

 

Thanks a lot! I did end up figuring out a way to do this. Since my data is being pulled in from Salesforce reports and those Salesforce reports are only showing a week's worth of data, I used two cards and put the earliest date in one card and the latest date in the other card. 

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors