Running a Stored Procedure for Multiple Dates in Power BI

Purpose:
To run the stored procedure get.myArchive from Power BI for a series of previous Sundays (for example, the last six Sundays), and optionally combine or parameterise the results.

---

🧭 Background

The get.myArchive procedure is designed to retrieve snapshot data for a specified date.
By default, Power BI passes a single date parameter such as:

= Sql.Database(DataMart_Host, DataMart_Endpoint, 
    [Query="EXEC get.myArchive 
        @dataMart = '" & Param_DataMart & "', 
        @viewName = '" & Param_DataMartView & "', 
        @token = '" & DataMart_Token & "', 
        @mydate = '" & Param_myDate & "', 
        @version = '" & Param_DataMartVersion & "'"])

However, when you need to analyse several past Sundays—for example, to view six weeks of archive data—you can dynamically generate the dates and either combine or parameterise the query.

---

🧮 Option 1 — Combine the Six Most Recent Sundays

This method loops through the last six Sundays, executes the procedure for each, and appends all results into one table.

let
    // 1️⃣ Generate list of 6 previous Sundays (including last Sunday)
    Today = Date.From(DateTime.LocalNow()),
    LastSunday = Date.AddDays(Today, -Date.DayOfWeek(Today, Day.Sunday)),
    SundayList = List.Generate(
        () => 0,
        each _ < 6,
        each _ + 1,
        each Date.AddDays(LastSunday, -7 * _)
    ),

    // 2️⃣ Convert the list into a table
    SundayTable = Table.FromList(SundayList, Splitter.SplitByNothing(), {"mydate"}),

    // 3️⃣ Run the stored procedure for each date
    AddResults = Table.AddColumn(
        SundayTable,
        "Data",
        each
            Sql.Database(
                DataMart_Host,
                DataMart_Endpoint,
                [
                    Query =
                        "EXEC get.myArchive " &
                        "@dataMart = '" & Param_DataMart & "', " &
                        "@viewName = '" & Param_DataMartView & "', " &
                        "@token = '" & DataMart_Token & "', " &
                        "@mydate = '" & Date.ToText([mydate], "yyyy-MM-dd") & "', " &
                        "@version = '" & Param_DataMartVersion & "'"
                ]
            )
    ),

    // 4️⃣ Combine all six result sets
    Combined = Table.Combine(AddResults[Data])
in
    Combined

🔍 Behaviour

• Calculates the last six Sundays (most recent first).
• Executes the stored procedure once per date.
• Combines all outputs into a single Power BI table.
• Each refresh retrieves six snapshots automatically.

---

🎚 Option 2 — Single-Sunday Parameter Selection

This method lets you refresh the report for a specific Sunday, selected from a dropdown list in Power BI.

1. Create a parameter query called Param_myDate: let Today = Date.From(DateTime.LocalNow()), LastSunday = Date.AddDays(Today, -Date.DayOfWeek(Today, Day.Sunday)), SundayList = List.Generate( () => 0, each _ < 6, each _ + 1, each Date.AddDays(LastSunday, -7 * _) ) in SundayList
2. Use it in the main query: = Sql.Database( DataMart_Host, DataMart_Endpoint, [ Query = "EXEC get.myArchive " & "@dataMart = '" & Param_DataMart & "', " & "@viewName = '" & Param_DataMartView & "', " & "@token = '" & DataMart_Token & "', " & "@mydate = '" & Date.ToText(Param_myDate, "yyyy-MM-dd") & "', " & "@version = '" & Param_DataMartVersion & "'" ] )

🔍 Behaviour

• Builds a dynamic list of six Sundays as valid parameter choices.
• Enables a user-friendly dropdown in Power BI.
• Runs the stored procedure only once for the selected date.

---

💡 Notes & Tips

• Use Option 1 for batch data collection or historical comparison.
• Use Option 2 when interactive filtering or on-demand refresh is preferred.
• Adjust the count in each _ < 6 to collect more or fewer Sundays.
• Date logic can easily be adapted for other weekdays or month-end dates.

---

🧾 Example Output

mydate	Project_ID	Cost_Base	Cost_Local	Currency	Version
2025-10-26	P-001	1,200	1,200	GBP	v2025.1
2025-10-19	P-001	1,195	1,195	GBP	v2025.1
2025-10-12	P-001	1,180	1,180	GBP	v2025.1

---

🧰 Summary

This approach allows Power BI to drive date iteration directly in Power Query, removing the need for manual reruns in SQL and ensuring consistent weekly snapshots from get.myArchive.