How to setup a stored procedure with incremental dates in Power BI

Moeez 0
Viewed 43

I am new to Power BI. I am using an SQL stored procedure to get the data.

execute ED_DS_TRANS_DETAIL01 @DateFrom ='2022-09-20', @DateTo = '2022-09-20'

It does give me the required data but I want it to be incremental. For example, today is the 20th and tomorrow is the 21st. So I want to set up the power bi in such a way that it gets the 21st data and then the next data 22nd and so on. Also, it doesn't replace the previous date data and places the next data underneath the previous one.

I have tried the given solution

let
  Source = (Query as text) => let
  Source = Sql.Database("IP", "DB" , [Query=Query, CreateNavigationProperties=false])
  in
  Source
  in
  Source

let
tdy = Date.From(DateTime.LocalNow()),
yest = Date.AddDays(tdy , - 1),
sQuery = Table.FromRecords({
    [sQuery = "execute ED_DS_TRANS_DETAIL01 @DateFrom ='" & Date.ToText(yest,[Format="yyyy-MM-dd"]) & "', @DateTo = '" & Date.ToText(tdy,[Format="yyyy-MM-dd"]) & "'"
    ]}),
#"Invoked Custom Function" = Table.AddColumn(sQuery, "Query2", each #"Fnc Query"([sQuery]))
in
#"Invoked Custom Function"

GUI

Enter image description here

When I click "OK" button I am getting Details: "Microsoft SQL: Incorrect syntax near '='."

powerbi-desktop powerbi
2 Answers

You can do it at the Power Query side...

First create the parameters of dBaseIP and dBase in case you may need to change it later...

Then create a custom function which will run the SQL query "New Query" → Other Sources → "Blank Query".

let
      Source = (Query as text) => let
      Source = Sql.Database(dBaseIP, dBase , [Query=Query, CreateNavigationProperties=false])
in
      Source
in
      Source

At last, create the below function which will update your query as per today's date...

    let
    tdy = Date.From(DateTime.LocalNow()),
    yest = Date.AddDays(tdy , - 1),
    sQuery = Table.FromRecords({
        [sQuery = "execute ED_DS_TRANS_DETAIL01 @DateFrom ='" & Date.ToText(yest,[Format="yyyy-MM-dd"]) & "', @DateTo = '" & Date.ToText(tdy,[Format="yyyy-MM-dd"]) & "'"
        ]}),
    #"Invoked Custom Function" = Table.AddColumn(sQuery, "Query2", each #"Fnc Query"([sQuery]))
in
    #"Invoked Custom Function"
Umut K0
userjpYe10
Related