Gift card balances on a past date

This report identifies which gift cards were active on a selected date and approximates their remaining balance at that time. It is most useful when reconciling gift card liabilities, preparing historical financial statements, or reviewing gift card activity for a previous period.

In this article

• How the report works
• Steps to build the report
• Interpreting the results
• Variations and extensions
• Need more support?

How the report works

The report is built on the Gift cards primary table, where each row represents a gift card issued through Shopify’s native gift card functionality. This is the correct table for this analysis as it contains the gift card-level fields — initial value, issued at, expires on, and disabled at dates — needed to reconstruct each card's status and balance at a point in time.

Three custom parameterised fields are required — all three use the same @Date    parameter so the selected date is applied consistently across the report:

• Amount spent up to @Date: the Gift cards table only records the total amount redeemed as of today, not at a point in the past. This field sums all gift card transactions up to and including the selected date to reconstruct the amount spent by that point.
• Balance on @Date: approximates the gift card's balance on the selected date by subtracting the Amount spent up to @Date from the gift card's initial value. Gift cards issued after the selected date return a balance of zero.
• Gift card active on @Date: returns True    for gift cards that were issued on or before the selected date and had not yet expired or been disabled at that date. Used as a filter to scope the report to cards that were active at the point in time being reviewed.

The report is filtered to show only gift cards that were active on the selected date and had a remaining balance greater than zero at that time.

Steps to build the report

Step 1 — Create the report

To create a new blank report on the Gift cards primary table:

1. Open the Explore tab
2. Select the Primary table selector above the Available fields pane
3. Select Gift cards to change the primary table

Step 2 — Create the Amount spent up to @Date custom dimension

This custom dimension sums all gift card transactions up to and including the selected date, returning one total per gift card. A subquery is required because the Gift cards table **only records the total amount redeemed as of today — the transaction-level data needed to reconstruct a historical total lives on the related Transactions table, joined at the gift card id level.

1. Select Edit, then + Create new, then Create Dimension
2. Set the Primary table to GiftCard
3. Set the Display name to Amount spent up to {@Date}
4. Set the Description to Sums transaction amounts for a gift card id up to and including the selected date
5. Set the Data type to Number and the Numeric type to Currency
6. Select + Add parameter and configure the @Date    parameter as follows:

7. Select OK to save the parameter
8. Enter the following calculation:

 (SELECT SUMZ([t.Amount])
  FROM [$.Transactions t]
  WHERE [t.IsPayment] = TRUE
  AND [t.Gateway] = 'gift_card'
  AND [t.Date.Day] <= [@Date]) 

9. Select Save

How the calculation works:

Step 3 — Create the Balance on @Date custom dimension

This custom dimension approximates the gift card's balance on the selected date by subtracting the Amount spent up to @Date from the gift card's initial value. Gift cards that were issued after the selected date return a value of zero.

1. Select Edit, then + Create new, then Create Dimension
2. Set the Primary table to GiftCard
3. Set the Display name to Balance on {@Date}
4. Set the Description to Approximates the balance of a gift card on a selected date by subtracting the amount spent up to the date from the initial gift card value
5. Set the Data type to Number and the Numeric type to Currency
6. Select + Add parameter and configure a @Date    parameter using the same settings as Step 2
7. Enter the following calculation:

CASE 
WHEN [$.IssuedAt.Day] <= [@Date]
THEN [$.InitialValue] - [$.AmountSpentUpToDate(@Date)]
ELSE 0
END

8. Select Save

How the calculation works:

Step 4 — Create the Gift card active on @Date custom dimension

This custom dimension returns true for gift cards that were issued on or before the selected date and had not yet expired or been disabled at that date. It is used as a filter in Step 6 to scope the report to cards that were active at the point in time being reviewed.

1. Select Edit, then + Create new, then Create Dimension
2. Set the Root table to GiftCard
3. Set the Display name to Gift card active on {@Date}
4. Set the Description to Returns true for gift cards that were issued on or before the selected date and had not yet expired or been disabled
5. Set the Data type to Yes/No
6. Select + Add parameter and configure the @Date    parameter using the same settings as Step 2
7. Enter the following calculation:

IS_TRUE(
[$.IssuedAt.Day] <= [@Date] 
AND ([$.ExpiresOn.Day] > [@Date] OR [$.ExpiresOn] IS NULL) 
AND ([$.DisabledAt.Day] > [@Date] OR [$.DisabledAt] IS NULL))

8. Select Save

How the calculation works:

Step 5 — Add fields to the report

Add the following fields to the report. When adding the custom parameterised fields, enter the date you want to report on when prompted. Use the same date for both fields — the field names will update automatically to reflect the date you selected, for example, Amount spent up to Dec 31, 2025 and Balance on Dec 31, 2025.

Step 6 — Add filters

Add the following filters using AND logic to restrict the report to gift cards that were active and had a remaining balance on the selected date:

1. Select Add filter, choose Gift card active on @Date, and set the operator to is Yes
2. When prompted, enter the same date used in Step 5
3. Select Add filter and choose the Balance on @Date field from the top of the list — this is the version already configured with your selected date
4. Set the operator to greater than and enter 0   

Step 7 — Sort the report

The report is sorted by Issued at date ascending by default, showing the oldest gift cards first. Adjust to suit your needs — for example, sorting by Balance on @Date descending shows the highest-value gift cards first, which is useful when reviewing total liability.

Step 8 — Save the report

Select Save and save the report with a name such as Gift card balances on [selected date]. Using the date in the report name makes it easy to distinguish saved snapshots if you run the report for multiple dates.

Interpreting the results

Each row represents a single gift card that was active and had a remaining balance as of the selected date. Review the balance values across rows to assess your outstanding gift card liability at that point in time — to see a single total figure, see Add a grand total row in the Variations and extensions section.

• A gift card appears in the report with a positive balance — the gift card was issued on or before the selected date, had not expired or been disabled, and still had a remaining balance. Include these gift cards when estimating your outstanding gift card liability as of that date.
• A gift card appears with a remaining balance even though it is no longer active today — this is expected. The report reflects each card's status and balance as of the selected date rather than its current state. Cards that have since expired, been disabled, or been fully redeemed will still appear if they had a positive balance on the selected date.
• Changing the selected date changes which gift cards appear or their balances — the report recalculates both the historical balance and active status using the selected date each time it is run. Running the report for multiple dates lets you track how your outstanding gift card liability has changed over time.
• A gift card you expected to see is missing — the card was likely fully redeemed before the selected date and has a balance of zero, which the balance filter excludes. It may also have been issued after the selected date, or expired or disabled before it. Check the filters in Step 6 if you need to confirm which conditions are applied.

Variations and extensions

Add a grand total row

To see the total balance and amount spent across all gift cards in the report, the Balance on @Date and Amount spent up to @Date dimensions need to be converted to measures before the grand total will calculate. Drag each field from the Selected dimensions pane into the Selected measures pane as a quick measure with the SUM aggregation. Then toggle the Show grand total checkbox.

Compare liability across multiple dates

Running the report for a single date gives a snapshot. To track how your outstanding gift card liability has changed over time — useful for monthly financial reporting or year-end reconciliation — run the report for multiple dates and save each version separately.

Each time you run the report for a new date, update the @Date    parameter across all fields and filters to the new date, then select Save as to save a new copy with the date in the report name.

Filter by gift card source

The Source field identifies the channel through which each gift card was issued — for example, online store, point of sale, or a third-party integration. Adding it to the report as a filter or dimension lets you break down your outstanding liability by issuing channel.

This is useful for stores that issue gift cards across multiple channels and need to understand the liability split — for example, separating online gift card liability from in-store gift card liability for reporting or reconciliation purposes.

To add it as a dimension, add the Source field to the report and adjust the sort hierarchy to group by Source first, then by Balance on @Date descending. To restrict the report to a specific channel, add Source as a filter instead and select the channel you want to analyse.