Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Add data from an SQL import with a date which is X days from today

I have a query in SQL Server (by way of an example)

 

sp_get_datafromtables @date = '2022-09-28' 

 

This is imported into PBI through the SQL Server import function, going in via the "SQL statement". 

However, I want to be able to call this query 4x to show a month's worth of data, spread across 4 weeks, but naturally dependent on the date the query is run or updated. The @date variable is fed to the query so that it captures data for the 7 days prior.

 

So, eg: 

 

sp_get_datafromtables @date = '2022-09-28'

sp_get_datafromtables @date = '2022-09-21'  

sp_get_datafromtables @date = '2022-09-14'

sp_get_datafromtables @date = '2022-09-07'  

 

Is there a way to get Power BI to automatically go back and dynamically change this, shifting up a day each time the query is run again the following day?

 

Thanks

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Anonymous ,

     

    There are several ways to dynamically get today's date in Power BI.

    Use DAX function today() or use M query datetime.localnow.

     

    Best Regards,

    Jay

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      This is inside the query source itself though. 

       

      To repeat / clarify, my query searches for data in tables for events that happened over the preceeding 7 days. I need to run the query with dates going back over the previous 28 days in four stages. I want, every time the query data is imported from SQL into PBI as part of a scheduled refresh, to have this rolling 4 week covered. 

       

      So far, I am at the following command, but I get DataFormat.Error: We couldn't parse the input provided as a Date value.

      Details:

      2022-09-14' (the apostophe is there, I don't know if this is relevant, but it's not part of the date value)

       

      Code:

      @date = '"& Date.FromText(Text.Start(DateTime.ToText(Date.AddWeeks(DateTime.LocalNow(),-4),[Format="yyyy-MM-dd", Culture = "en-US"],10)&"'")])

       

      The other query would be the same, but -3, then another one -2, etc.

       

      Does this make things clearer?