How to Calculate Growth/Decline Of Two Values Dynamically

Question & Answer
1

How can I calculate the growth or decline between two numbers dynamically?

I need to be able to enter a start and an end date in the Quicksight Dashboard and dynamically calculate the growth or decline of an account (see below).

The way it is supposed to work is as follows:

  1. The start date retrieves the opening balance for the requested period. For example, SUM({opening_balance}, [start_date]) (on 2022-02-01 the opening balance is $2,000.00).

  2. The end date retrieves the closing balance for the requested period. For example, SUM({closing_balance},[end_date]) (on 2022-08-01 the closing balance is $9,000.00).

  3. The final calculation the brings step 1 and 2 into a final formula to derive the ratio.
    [closing_balance] - [opening_balance] / [opening_balance]

image
image2384×543 36.9 KB
.

Thanks

2

Here’s how to get the max date.

ifelse(maxOver({arrival_timestamp},[],PRE_AGG)={arrival_timestamp},'Last Date','Not Last')

You can use this to return a value

ifelse(maxOver({arrival_timestamp},[],PRE_AGG)={arrival_timestamp},{value},0)

Do this for min as well and then subtract them.