Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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 dat...
Need Help
  • v-kkf-msft's avatar
    v-kkf-msft
    5 years ago

    Hi Anonymous ,

     

    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

     

    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.

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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?

v-kkf-msft's avatar
v-kkf-msft
Icon for Community Support rankCommunity Support
5 years ago

Hi Anonymous ,

 

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

 

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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.