Period Over Period and Other Date Comparison Calculations (YoY, MoM, YTD, Rolling 90 days, etc)

[Jesse]

Often you may want to create tables and other visuals which display multiple fields that are all ‘filtered’ to different date periods, as well as period-over-period fields. However we can’t use normal filters for this since filters get applied to all fields in the visual. Conceptually in this solution we are building the filters directly into the calculated field itself (using the ifelse logic), so that each field can have a different ‘filtered’ time period. You can then do whatever math you want on top of these fields, like Year Over Year, % Differences, etc. Aaaand one of the best parts is everything will update dynamically so you never need to touch them again!

Desired output:

Below you will find a list of common date comparison calculations. All references to the field ‘sales’ can be replaced with the metric you wish to display. If you dont want all calculatinos to be relative to today, aka now(), you can replace all references of now() with a date parameter like ‘${AsOfDate}’ which will allow the user to pick the date they want to anchor the ‘end date’ to. Or, if you want to base it of the latest date in your data set, then first create a calculated field like this and reference that date field as your end date instead:

Max Date:
maxOver({your date field}, [ ], PRE_AGG)

Between dateDiff(), addDateTime(), truncDate(), and extract() you can define whatever periods you want (and there are often multiple ways to do each one, these are just some proven examples).

Today:

ifelse(dateDiff({order_date},now()) = 0,sales,0)

Yesterday:

ifelse(dateDiff({order_date},now()) = 1,sales,0)

Last 90 Days:

ifelse(dateDiff({order_date},now()) <=90, sales,0)

This week: (Sun to today)
ifelse(truncDate(‘WK’, {order date})= truncDate(‘WK’, now()) {order_date}<=now(), sales, 0)

Last Week: (Last Sun to last Sat)
ifelse(truncDate(‘WK’, {order date})= addDateTime(-1, ‘WK’, truncDate(‘WK’, now())), sales, 0)

Last WTD: (Last Sun to today’s weekday of last week)
ifelse(truncDate('WK', {order date})= addDateTime(-1, 'WK', truncDate('WK', now())) AND {order date}<=addDateTime(-1, 'WK', now()), sales, 0)

This year (aka Year to Date, YTD):

ifelse(dateDiff({order_date},now(),"YYYY") = 0 AND {order_date}<=now(), sales, 0)

Last year:

ifelse(dateDiff({order_date},now(),"YYYY") = 1, sales, 0)

Last year to date (aka LYTD):

ifelse(dateDiff({order_date},now(),"YYYY") = 1 AND {order_date} <= addDateTime(-1,"YYYY",now()),sales,0)

This month (aka Month to Date, MTD):

ifelse(dateDiff({order_date},now(),"MM") = 0 AND {order_date}<=now(), sales, 0)

Last month:

ifelse(dateDiff({order_date},now(),"MM") = 1, sales, 0)

Same month last year:

ifelse(dateDiff({order_date},now(),"MM") = 12, sales, 0)

Last month to date (aka LMTD):

ifelse(dateDiff({order_date},now(),"MM") = 1 AND {order_date} <= addDateTime(-1,"MM",now()),sales,0)

Month to date last year (aka LYMTD):

ifelse(dateDiff({order_date},now(),"MM") = 12 AND {order_date} <= addDateTime(-1,"YYYY",now()),sales,0)

This Quarter:

ifelse(dateDiff({order_date},now(),"Q") = 0, sales, 0)

Last Quarter:

ifelse(dateDiff({order_date},now(),"Q") = 1, sales, 0)

Same Quarter Last Year:

ifelse(dateDiff({order_date},now(),"Q") = 4, sales, 0)

MoM % Diff:

(sum({This Month}) - sum({Last Month})) / sum({Last Month})

YoY % Diff:

(sum({This Year}) - sum({Last Year})) / sum({Last Year})

Monthly YoY % Diff:

(sum({This Month}) - sum({This Month Last Year})) / sum({This Month Last Year})

QoQ% Diff:

(sum({ This Quarter}) - sum({Last Quarter}))/sum({Last Quarter}))

[Kashgar]

One of the best summaries I’ve seen for this. THANK YOU!

[Ashish_Arora]

Thank you for sharing this solution.

[Joshua_L_Dobson]

You having “Rolling 7 Days” in your title, but provided no examples. I am currently trying to figure out how to write a rolling seven day distinct count calculation, but finding nothing. Any ideas?

Josh

[Jesse]

@Jesse

ifelse(datediff({order_date},${AsOfDate}) <=90, sales,0)

Hi Joshua - it would be similar to the last 90 days calc, but use 7 as the number and return the field you want to do a distinct count of instead of Sales, and return NULL instead of 0. Then when you drag and drop it into the visual choose a Count Distinct, or in your calculation wrap the whole thing with distinctCount().

[rbrady]

@Joshua_L_Dobson

out how to write a rolling seven day distinct count calculation, but finding nothing. Any ideas?

Josh

What if my table has date as a column? something like this

wouldn’t it just show those values as zero rather than actually filtering the unwanted rows out?

[Jesse]

Hi rbrady - apologies not sure I understand. With these calculations we are trying to avoid filtering out rows because each column in the final table needs to show different periods (and hence different rows). So you are right that any rows outside of the range we define using the dateDiffs will have a value of 0, but thats what we want (becuase we might needs those rows for a different column/calculation and cant filter them out). If you actually want to filter the rows out then I would recommend using a Relative Date Filter.

[rbrady]

Sorry for my delayed response. My goal is to have a table that shows only rows which have dates within a specific range specified by controls. However the rows that are shown are not the only ones being used for calculations. For example lets say I set my controls to only the month of May. So my table is only showing rows for 5/1/2022 - 5/31/2022. But in my table I have a column “sum of previous month” which shows me what my sales were on each day of the previous month. So even though I only want my user viewing rows with dates 5/1/2022 - 5/31/2022, I don’t necessarily want to filter out records with dates 4/1/2022 - 4/30/2022, because then my “sum of previous month” column for May won’t be able to access the values from April.

[Tanisha_Shetty]

Hi Jesse, How do we find out the periodic differences when the field is a calculated field and is in percentage terms?

[Jesse]

@Tanisha_Shetty you would need to build that ifelse logic into the numerator and denominator for the calculations that compute your percentage, then do the period over period comparison calc using those. Something like:

• Period 1 percentage: sum(ifelse(whatever logic for period 1, numerator, 0)) / sum(ifelse(whatever logic for period 1, denominator, 0))
• Period 2 percentage: sum(ifelse(whatever logic for period 2, numerator, 0)) / sum(ifelse(whatever logic for period 2, denominator, 0))
• Period over period: (Period 2 / Period 1) - 1

[Jesse]

@rbrady - I see, this is a little different because you actually want to show multiple date values in the table, along with the period over period comparisons, so your date field will wind up being part of your GROUP BY and the technique describe in this post wont work. You can acheive what you want by using Table Calculation functions to do your period over period calcs (not like the technique in this post, but rather using functions like periodOverPeriodDifference and periodOverPeriodPercentDifference, and then change your filter into a ‘hider’ by filtering on a Table Calculation version of your date field, which will cause that filter to happen later in the query pipeline and will result is keeping all your data in the query and just ‘hiding’ the data points you don’t want at the end. That last step is covered in this post:

How do I superimpose a period over period in a visual without having the period that I'm comparing it to in the visual?

We conceptually need to ‘hide’ those values and not filter them. To do this we will manipulate where this filter happens in the query pipeline, and by using a Table Calculation, we can make it happen all the way at the end, after the YoY calcs have already been computed. Write a calc like this: minOver(min({order date}), [{order date}]) And then put your filter on that. Should work! Before: [image] After: [image]

[rbrady]

I see, thank you for the direction!

[Tanisha_Shetty]

Thanks Jesse, I will try to implement this.

[ShrysNyk]

@Jesse

Or, if you want to base it of the latest date in your data set, then first create a calculated field like this and reference that date field as your end date instead

Hi Jesse, if I want this to be based on a filter selection, how can I go about this? I have a governing date filter applied across the dashboard and I want the input selected there to be the base date.

[Jesse]

Hi @ShrysNyk - You will need to use a parameter and expose a control for it rather than use a filter on this visual. You can still use the same parameter to drive the filters on your other visuals in the dashboard (when you add your date filter you will see options to have it driven by a parameter), but for the table you are building you will use the parameter in your calculated fields (like how they are written in the original post using the AsOfDate parameter).

So to be clear, you wont have any filter controls on your dashboard, only the parameter control.

[David_faola]

Hello Jesse, kindly see attached image. I used calculated fields to get all the figures. The figures change every day after an update.
On another page I want to use a date picker that will give me exactly these figures for the particular day (in the past) selected from the date picker. I have tried using parameter with date filter but I have not been successful. Any ideas please?
David

[Jesse]

Hi @David_faola you dont need any date filters. Just the parameter, with a control for it, to pick the ‘as of date’ and then all the calculations in the original post will update relative to that date.

[ccowen]

Hi @Jesse , What is ${AsOfDate}? Is this a parameter you created or something built in?

[Jesse]

HI @ccowen - yes that is a parameter I created called AsOfDate

[sanmar]

@Jesse Hi
I use FISCAL Periods. Each month has a different start and end date for the calendar. June for instance Starts on 4 June and Ends on 1 July
I have in my database each date of start and end.
Each month is different in number of days.
My database is updated daily with yesterday’s date, at 12 am.
I did your calculated fields for the month, quarter and year The calculation it worked Ok during June, but on July 1th everythin was reset.
Month like this ifelse(datediff({fiscalmesinicio}, now(), “MM”)= 0,{sales}, 0)

I should replace now() with something else But I’m not sure
And on the other hand, I would need to calculate the last month, quarter and annual periods of the previous year but up to date.
Can you help me?
Thanks in advance