Hello @WLS-DM
I have dataset for donation
| Donation ID | Donor Name | Donation Amount | Amount Received Date | Donation Date |
|---|---|---|---|---|
| 57163600985 | Adeline | $203.00 | 12/8/2025 | 12/7/2025 |
| 50752367267 | Adelaide | $218.00 | 12/9/2024 | 12/9/2024 |
| 75388114241 | Bonnie | $168.00 | 12/4/2025 | 12/6/2025 |
| 67890901716 | Britney | $89.00 | 12/1/2025 | 12/1/2025 |
| 12419690315 | Courtney | $267.00 | 12/3/2025 | 12/6/2025 |
| 29878999574 | Catherine | $193.00 | 12/2/2025 | 12/8/2025 |
| 39322136167 | Devin | $113.00 | 12/9/2024 | 12/7/2025 |
| 1035821579 | David | $213.00 | 12/5/2025 | 12/8/2024 |
I have added the filter Amount received date to be the filter. However, I would like the values in the filter to change dynamically depending on what today is.
I would like the datetime value to be changed dynamically depending on what is today’s date. My organization’s financial year is 1st April of calendar year to 31 Mar of the next calendar year.
If today is 31st Mar 2024, I would like the value in A to be 1st Apr 2023 and value in B to be 31 Mar 2024.
If today is 1st April 2024, I would like the value in A to be 1st Apr 2024 and value in B to be 31 Mar 2025.
I have set up two parameters (i.e. startofcurrentFY and endofcurrentfy) and two calculated fields to be used for value A and value B, such that I can use parameters for the filter amount received date to and amount received date from (i.e. A and B)
for value A, the calculated measure startofcurrentFY =
ifelse(
truncDate(‘YYYY’, now()) = ${startofcurrentFY}
AND extract(‘MM’, now()) >= 4
OR
truncDate(‘YYYY’, addDateTime(1, ‘YYYY’, ${startofcurrentFY})) = truncDate(‘YYYY’, now())
AND extract(‘MM’, now()) < 4,
parseDate(concat(toString(extract(‘YYYY’, now())-1), ‘-04-01’), ‘yyyy-MM-dd’),
parseDate(concat(toString(extract(‘YYYY’, now()) ), ‘-04-01’), ‘yyyy-MM-dd’)
)
for value B, the calculated measure endofcurrentFY =
ifelse(
truncDate(‘YYYY’, now()) = ${endofcurrentfy}
AND extract(‘MM’, now()) >= 4
OR
truncDate(‘YYYY’, addDateTime(1, ‘YYYY’, ${endofcurrentfy})) = truncDate(‘YYYY’, now())
AND extract(‘MM’, now()) < 4,
parseDate(concat(toString(extract(‘YYYY’, now())), ‘-03-31’), ‘yyyy-MM-dd’),
parseDate(concat(toString(extract(‘YYYY’, now())+1 ), ‘-03-31’), ‘yyyy-MM-dd’)
)
Problems:
- However when I used the two parameters I have set up in the amount received date filter, i see that it return Null for Amount Received date to and Amount Received from in the control.
- When I remove Amount Received date to and from from the filter, and added the parameter to the control, I have to pick a static defult date that does not change dynamically with what today;s date is.
My problem is actualy very similar to the below:
How to make Dynamic parameter - Question & Answer - Amazon Quick Sight Community