Mom/QoQ calculations using last date of each period

Question & Answer
, , , ,
1

I want to calculate MoM and QoQ for my metric, but using the latest date in each period. For example, for MoM, find the difference in the metric between latest date in current month and latest date in previous month, where latest date = max of the date field for each period in my dataset. How can I do this?

2

Hi
Follow the following steps to achieve your results.
Create the month_key field: truncDate(“MM”, {Date})
Create the quarter_key field: truncDate(“Q”, {Date})
Create the latest_date_in_month field:maxOver({Date}, [{month_key}], PRE_AGG) Create the latest_date_in_quarter field: maxOver({Date}, [{quarter_key}], PRE_AGG)
Create the is_latest_date_in_month field: ifelse({Date} = {latest_date_in_month}, 1, 0)
Create the is_latest_date_in_quarter field: ifelse({Date} = {latest_date_in_quarter}, 1, 0)
Apply the filters:

  • Go to the dataset and apply the filter is_latest_date_in_month = 1 to focus on the latest dates for each month.
  • Similarly, apply the filter is_latest_date_in_quarter = 1 for the latest dates for each quarter.
    Create the prev_month_metric field: lag({metric}, [{month_key}], 1)
    Create the mom_change field: {metric} - {prev_month_metric}
    Create the prev_quarter_metric field: lag({metric}, [{quarter_key}], 1)
    Create the qoq_change field: {metric} - {prev_quarter_metric}

By following these steps, you should be able to calculate the Month-over-Month and Quarter-over-Quarter changes for your metric based on the latest dates in each period

3

Hi @Bhavya_Jain, did this solution work for you? I am marking this reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community!