I have a dataset with the following:
DS date fieldA
StationA 2023-05-16 20
StationA 2023-05-17 30
I am trying to build a table contaning two measures from the field A, but they need to show two different dates:
1- one measure (sum of a field) showing date filtered by the user (ex: 2023-05-17) = 30
2- one measure (sum of same field from #1) showing 1 day prior to the filter (in that case, 2023-05-16) = 20
output would look like this:
DS fieldA(d0) fieldA(d1)
StationA 20 30
I tried using parameters, but it is not working well.
Hi @Alexandre_Kniepert,
Please take a look at, periodOverPeriodLastValue function in QuickSight. This provides a way for you to get the previous metric value by passing the date and granularity for comparison.
More details can be found here: periodOverPeriodLastValue - Amazon QuickSight
2 Likes
Hi,
thanks for the reply. That would partially solve the issue, because such measures require a filter being used in two dates, d0 and d-1. Besides, they also need to be in the table, which means now I have one column blank and another column with the values - see pic below.
I am looking for a view where I would have the same as in the pic, but without may,16. The date does not need to be there as well.
In the filter, it would also need to be more useful to be able to mark only 1 ofd date, in that case may 17, rather than having a range of dates to capture d-1.
in Power BI this would be simple, a measure would consider value X when ofd date = ofd date of a filter minus 1.
any ideas?
If I filter out the null values then I remove the column from may 16, which would solve my issue. See Pic: now I only see the d-1 related to May17, so this works.
However I would be still interested to know whether there is another way of doing it - also because in the table I would like to have measures from two different times: d-1 and d+1.
So, for a given ofd_date, say May17, I need some measures that consider may 16 and some measures that consider may 18. With the current solution the filter for the user would need to consider three dates. besides, now I will have values for two different dates and ranges, because QS will not know which ofd is d0 anymore.
it gives me this
Therefore there must be a better way to create a measure that considers ofd_date related to a selected ofd_date. again: I filter for ofd_date may17, I want to see some measures with d-1 and some measures with d+1 on a clean table
I have a table like this already, but only related to ofd = now(), therefore not chosen by the user.
like this (for d-1):
sumif({measure},dateDiff({ofd_date},now())=1)
