I'm working with amazon-quicksight and I'm trying to migrate one funcionality from PowerBi to Quicksight but it has not been possible.
The idea is: I have a dataset with two columns "date" and "sales". The user will have a filter with the column "date". More than one date can be selected. Depending on the dates selected by the user we need to get two KPIs, the first one is the sum of sales for those dates (this is already done), but the second one is my problem, it should be the sum of the sales on the same days selected by the user but in the previous year (it depends on which year was selected for each date).
Example:
DataSet:
| DATE | SALES |
|---|---|
| 2020-01-05 | 1 |
| 2020-02-01 | 1 |
| 2020-06-10 | 4 |
| 2020-06-17 | 1 |
| 2021-01-01 | 1 |
| 2021-02-01 | 3 |
| 2021-06-10 | 3 |
| 2021-06-15 | 5 |
If the user select the dates: 2021-02-01, 2021-06-10 and 2021-06-15, the result should be:
KPI 1: Sum of sales (for those dates): 11
KPI 2: Sum of sales for those dates in the previous year: 5 -> (Days to use 2020-02-01, 2020-06-10 and 2020-06-15)
Do you have any idea about how can I calculate the KPI 2? Any suggestion?
In powerBI this (KPI 2) was done with the function: SAMEPERIODLASTYEAR
Thanks in advance.
I was able to solve this issue taking into account that the user must filter data from the two years, for example 2020 and 2021.
After that, I created the "calculated field":
calc_sum_sales = sum(sales)
After that:
calc_sum_sales_last_year = ({calc_sum_sales}-periodOverPeriodDifference(sum(sales),date,YEAR,1))
After that, I created an "visual table" and added in "group by" the field "date" and in "value" the field "calc_sum_sales_last_year"
I must give click in the column date to agroup by "year". In that way I get just two rows, one row for each year.
Finally, I change the "visual table" to "KPI" and I got the expected result.