Compare Two Time Spans Dynamically (current vs. same time one year ago)

Hi,

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:

  1. User selects 11/15/2022 - 12/15/2022 as current time span on the UI date filter
  2. Calculate same time span one year ago = 11/15/2021 - 12/15/2021
  3. 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

Any ideas how to do that?

Regards,
David

you can look at period over period percent difference.

Or a lag function that groups to a date range.

Also look at this for grouping to a date range.

Hi Max,

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?

Regards,
David

Yeah I think that could work as well if you are aggregating. Nice solution

1 Like