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

 

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.

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Anonymous , 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

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