Showing revenue generated this day a year ago, month to date a year ago, and year to date a year ago

Question & Answer
, ,
1

I have revenue and occupancy data (percentage ) from Jan 01, 2021 to date i.e Jan 28, 2022.

I want to tackle 3 problems

  1. I want to show revenue generated for this day a year ago (i.e. last year - Jan 28, 2021), calculate revenue month to date for that date, and revenue year to date for that date.

  2. Also do the same for the occupancy data,

  3. Compare revenue generated a year a go to revenue generated today in a KPI, and also same for the occupancy data

I hope it is not as complicated as i think it is.

2

revenue on this day 1 year ago: sumIf( {revenue_field}, {date_field} = addDateTime( -1, ‘YYYY’, truncDate( ‘DD’, now())))

month to date 1 year ago: sumIf( {revenue_field}, {date_field} >= addDateTime( -1, ‘YYYY’, truncDate( ‘MM’, now())) AND {date_field} <= addDateTime( -1,‘YYYY’, truncDate( ‘DD’, now())))

year to date 1 year ago: sumIf( {revenue_field}, {date_field} >= addDateTime( -1,‘YYYY’, truncDate( ‘YYYY’, now())) AND {date_field} <= addDateTime( -1, ‘YYYY’, truncDate( ‘DD’, now())))

current date revenue: sumIf( {revenue_field}, {date_field} = truncDate( ‘DD’, now()))

Try these calculated fields. For the KPI I would just add “current date revenue” as the value and “revenue on this day 1 year ago” as the target value.
It also might be worth looking into the new period to date, and period over period functions. I didn’t use them here because I haven’t had a chance to try them out yet. Period to date computation - Amazon QuickSight

1 Like
3

Thanks, the new Period to date functions was also helpful.

1 Like