I was working with a customer recently and they explained that their important metrics are measured by End of Day, Month To Date, Quarter to Day and Year to Date, and they wanted to see these metrics based on a selected date.
There are several very good examples of how to use the Quicksight PeriodToDate functions (for instance, see here and here) but those don’t work for an arbitrary selected date. Ideally, you can select any date from a calendar control and the PeriodToDate function will calculate the metrics up to that date/time. Most of the examples default to use now() as the end date and simply passing in the value for the date control was not giving me the expected results.
It took me a little time to find the right date function to calculate the end date and I’m hoping this post will save others that effort.
Here’s the end result:
The KPIs on the top are the calculated fields using the PeriodToDateSum() function with the appropriate time period value and the bottom tables show the same Sale metric aggregated by the same period. This shows that the calculated fields are correct.
And these change based on the selected date. Here is it for just one day in the past:
And for the previous quarter:
To make this work, I used the following as the end date:
addDateTime(dateDiff(now(), ${pAdjustedDate}, 'DD'), 'DD', now())
And each KPI was simply changing the time period, e.g. Sales MTD:
periodToDateSum(
Sales,
AdjustedSalesDate,
MONTH,
addDateTime(dateDiff(now(), ${pAdjustedDate}, 'DD') , 'DD', now())
)
and Sales QTD:
periodToDateSum(
Sales,
AdjustedSalesDate,
QUARTER,
addDateTime(dateDiff(now(), ${pAdjustedDate}, 'DD') , 'DD', now())
)
The AdjustedSalesDate is the trick described in the link above by this:
addDateTime(dateDiff(maxOver({Sales Date}, [], PRE_AGG), now()), 'DD', {Sales Date})
Now I can’t wait to see how the new ‘Build For Me’ features handles this!
Hope that helps.
ws