Amazon QuickSight – periodToDateAvg

The periodToDateAvg() function in Amazon QuickSight is used to compute the average value of a measure over a specified period, up to the current date or an optional end date. Here is an explanation of the periodToDateAvg() function:

Syntax

#Start#
periodToDateAvg(measure, dateTime, period, [endDate])
#End#

This function takes one argument:

measure: This parameter specifies the measure to be averaged over the specified period.

dateTime: This parameter specifies the date/time field to use as the basis for the period. This should be a date or datetime field.

period: This parameter specifies the period to be used for averaging. The allowed values for this parameter are: ‘HOUR’, ‘DAY’, ‘WEEK’, ‘MONTH’, ‘QUARTER’, ‘YEAR’.

[endDate]: This parameter is optional and specifies the end date for the period. If it is not specified, the period ends at the current date.

Suppose we have a dataset that contains information about daily sales revenue for a company and we want to compute the average daily sales revenue for the current quarter. We can use the periodToDateAvg() function in Amazon QuickSight to do this.

To compute the average daily sales revenue for the current quarter, we can create a new visual in Amazon QuickSight and add a value field with the following settings:

Drag the “Sales Revenue” measure to the Values section.
Click on the drop-down arrow next to “Sales Revenue” and select “Period-to-Date Average”.
In the “Date/Time Field” field, select the date or timestamp field that represents the sale date.
In the “Period” field, select “Quarter”.
Leave the “End Date” field blank.

This will result in a value that shows the average daily sales revenue for the current quarter.

Alternatively, we can create a calculated field in Amazon QuickSight with the following expression

Example

#Start#
periodToDateAvg(SalesRevenue, SaleDate, 'Quarter')
#End#

This expression will compute the average daily sales revenue for the current quarter.

For instance, if our sales data is as follows:

Sale Date	Sales Revenue
2022-01-01	10000
2022-01-02	20000
2022-01-03	15000
2022-01-04	18000
2022-01-05	12000
2022-01-06	9000
…	…
2022-03-30	17000
2022-03-31	22000

Then, the average daily sales revenue for the current quarter will be:

Example

#Start#
periodToDateAvg(SalesRevenue, SaleDate, 'Quarter') = (total sales revenue for current quarter) / (number of days in current quarter)
total sales revenue for current quarter = sales revenue for 2022-01-01 to 2022-03-31 = 1020000
number of days in current quarter = 90
periodToDateAvg(SalesRevenue, SaleDate, 'Quarter') = 11333.33
#End#

In this example, we can see that the periodToDateAvg() function has computed a useful metric that can inform our business decisions. Note that we can also specify an end date to compute the period-to-date average up to a