Amazon QuickSight – periodToDateMedian

The ‘periodToDateMedian()’ function in QuickSight is used to calculate the median of a given measure over a specified period up to a specified date.

Syntax

#Start#
periodToDateMedian(measure, dateTime, period, endDate)
#End#

This function takes the following arguments:

measure: Required. The name of the measure field that you want to calculate the median for.

dateTime: Required. The name of the date/time field that represents the date/time for which you want to calculate the median.

period: Required. A string representing the period over which you want to calculate the median. Valid values are “hour”, “day”, “week”, “month”, “quarter”, or “year”.

endDate: Optional. The end date of the period. If not provided, the current date is used.

Suppose you have a sales dataset with the following columns: “Order Date”, “Product Category”, “Sales Amount”. You want to calculate the median sales amount for the “Electronics” category for the current year up to today’s date. You can use the periodToDateMedian() function as follows:

Example

#Start#
periodToDateMedian(Sales Amount, "Order Date", "year")
#End#

This will calculate the median sales amount for the “Electronics” category for the current year up to today’s date. If you want to specify a different end date, you can add the endDate parameter:

Example

#Start#
periodToDateMedian(Sales Amount, "Order Date", "year", "2022-03-15")
#End#

This will calculate the median sales amount for the “Electronics” category for the 2022 calendar year up to March 15th, 2022.