I would like to create a KPI visual that shows the change of a metric compared to the same time range one year ago.
So, for instance:
User selects 11/15/2022 - 12/15/2022 as current time span on the UI date filter
Calculate same time span one year ago = 11/15/2021 - 12/15/2021
Calculate percentage change like sum(current time span / past time span)
I already created a calculated field, but this does not use a dynamic time span. I some way I have to get paramters into the caluclation to get the selected start and end dates of the user:
(sumif(Metric, Date >= truncDate('YYYY',now()) AND Date <= now())
/
sumif(Metric, Date >= truncDate('YYYY',addDateTime(-1, 'YYYY', now())) AND Date <= addDateTime(-1, 'YYYY', now()))) -1
thanks for your suggestions! Following my current approach I created two date parameters startDate2 and endDate2. Based on the selected parameter values I tried to calculate the difference between the current selection of dates and the same dates one year ago. This is the underlying calculation:
(
sumIf(
Metric, Date >= ${startDate2} AND Date<= ${endDate2}
)
/
sumIf(
Metric, Date >= addDateTime(-1, 'YYYY', ${startDate2}) AND Date<= addDateTime(-1, 'YYYY', ${endDate2})
)
) -1
I tested it within my dummy dashboard and it seems to work properly, are there any constraints you see regarding this calculation?