Sum of current month till date i.e. max of date in data same for Previous months

Question & Answer
, ,
1

Hi, is there any way we can create a metric field where we can get the data for current month till date same for previous month where last day for previous month should be same day number of current month.
Eg.
I have total data from 1st February 2024 to 10th of May 2024. For current month i.e. May I have data from 1st May 2024 to 10th May and total count for current month we calculated from 1st to 10th May. I’m calculating in this way

sumIf({Count}, MONTH={latest_month})

where MONTH is month number extracted from date and latest month is max(month) from latest_date i.e.

maxOver({DATE}, [ ], PRE_AGG)

Same date range i.e. 1st to 10th for all previous months we need how we can get any field logic please share.

1 Like
2

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.

1 Like