2 Replies Latest reply on Jan 3, 2018 9:39 AM by Brandon Thomson

    Comparing a measure against a prior period

    Brandon Thomson

      Hi everyone,


      I am creating a marketing dashboard that aims to compare performance for certain measures against the previous period. Ideally, we want our client to be able to compare against a custom period and not be constrained by having to select prior week, prior month, or prior year. For example, if we are looking at brand interactions from November 1 - November 15 I would then like the field "Prior Period Interactions" to be the sum of interactions from October 17 - October 31. So instead of looking at a static 7 days, 30 days, or 365 days, the # of days in the prior period is calculated based off of the dates they select. Ideally, I would like to use the Range of Dates slider that is built-in. Though, I'd be wiling to use a "Start Date" and "End Date" parameter if it's required.


      So far, I've been trying to use the DATEADD function to create a "new" set of dates to calculate based off of. Essentially, # of days = window_max(Max([Report Date]))-window_min(Min([Report Date])). Then, Prior Period Start = DATEADD('day',-[# of days]-1,MIN([Report Date])) and Prior Period End = DATEADD('day',-[# of days]-1,MAX([Report Date])). Now, I want to calculate the value of "Brand Interactions" between those two dates. Of note, the field "Brand Interactions" is an aggregate of measures from 4 different data sources, so LOD calculations may not work.


      Attached is a dummy workbook with example data.


      Thanks in advance,