Hello @vinay_revankar, is there a scenario where the most recent date won’t be the date of today? If not, we can use now() which is a QuickSight operator to grab today’s date. Then we can use that to retrieve this month to date and last month to date. I’ll write some calculated field logic below:
This will return our count field if they are within this month up to today.
This month to date =
ifelse(
truncDate('MM', now()) = truncDate('MM', {DATE}) AND truncDate('DD', now()) >= truncDate('DD', {DATE}), {Count},
NULL)
This will return our count field if they are within last month up to the same end day in that month. We can use the addDateTime function to handle this logic.
Last month to date =
ifelse(
addDateTime(-1, 'MM', truncDate('MM', now())) = truncDate('MM', {DATE}) AND addDateTime(-1, 'MM', truncDate('DD', now())) >= truncDate('DD', {DATE}), {Count},
NULL)
Now, if you wanted to see the sum for last month and the sum for this month, you can aggregate them like this:
This month total = sumOver({This month to date}, [], PRE_AGG)
Last month total = sumOver({Last month to date}, [], PRE_AGG)
One thing to remember, this will return the same value on multiple rows, so if you want the actual value in a visual, use the min aggregation in the field well. I will mark this as the solution, but please let me know if you have any questions.