How to calculate difference between 2 values in pivot table

I would like to know difference percentage for the median price for the 2 dates

Hi @Farzana_Mohammed,

Is this direct query (if so, what’s the data source) or SPICE? I will have to do some digging on my to see if this is possible with pivot tables in QuickSight.

Typically for this kind of problem I’d solve it at the datasource level with a query using a window() function. You would

  • sort your records by run_date
  • create partitions using Procedure_code,Procedure_name,Palce_of_Service_ref
  • then iterate over the records and subtract Median_price of the date before (May 19th in your example) from the current date (May 23).
1 Like

@eperts Yes its direct query. Are you suggesting to use window() function in Quicksight?

@Farzana_Mohammed Try this

  • Create a calculate field and add
difference(sum({median_price}),[{run_date} DESC],1,[{procedure_code}, {procedure_name}])

I added some more data in my dataset to test this works. May 19th being the first date won’t have a value.

If this answers your question please mark the answer as solved.