Exisitng rows as derrived column

Question & Answer
, ,
1

I have a dataset with following columns

date value productgroup
20240131 123 ABC
20240131 456 DEF
20231231 789 ABC
20231231 019 DEF

I have a filter dropdown for date= 20240131. I want another calculated column which is end of previous month ( 20231231 ) so report should look like below. And if I change the filter to 20231231 , it should show me 20231231 as current and 20231131 previous and so on. What is the way to achieve this in quicksight?

CurrentMonth Previous Month CurrentValue previousvalue productgroup
20240131 20231231 123 789 ABC
20240131 20231231 456 019 DEF

What function can help me do this in quicksight?

Thanks,
Rohit

2

Hello @kochar, welcome to the QuickSight Community!

Yes, the perfect function for this to return your previous month value on every row would be to utilize the addDateTime calculation.

The function would look something like this:
PreviousMonth = addDateTime(-2, 'MM', {CurrentMonth})

I will mark this as the solution, but let me know if you have any follow-up questions on this topic. Thank you!

3

Thanks! But will this fetch data from the dataset or already filtered data wich was selected from dropdown?

4

Hello @kochar, this calculation should function with the filters you are applying to the visual. It will be based on the CurrentMonth value that is being returned on that particular row.

1 Like