I'm struggling with Period-on-Period calculations.
Firstly, it's worth saying users can select multiple combinations of filters including Date. If they only choose, say, 2023, then all year on year Table Calc information is lost so that's not an option.
I'd like users to be able to pick any time period and choose between seeing the equivalent dates last year (YoY) AND the equivalent immediately preceding period (PoP). I've seen various examples online, but they always have pre-set time periods (months, years etc) rather than user-defined time periods. Also, they all need to be set up for each metric, and I have about 40, so I'm looking ideally for a solution that doesn't require the metric within the calculation.
For PoP, I'm imagining the user making a date selection using the Date Range Filter, then maybe using a calculation looking at the min selected date, counting the days to the max date, then using that information to count back the relevant number of days from the min selected date to give the equivalent previous period, then adding another calculation to find the % change. I'm sure I need LOD terms in there somewhere too, but it's just too many moving parts. I've tested various alternatives and nothing has worked properly, so maybe I'm tackling this completely wrong.
Am I being too greedy? Thanks in advance for anybody taking the time to read this. If you have any ideas, I'd love them!
Thanks again @Jim Dehner .
The 2nd example is useful for YTD and I've successfully implemented it (parameters and calculated fields prefixed with "TP_" in the attached workbook "PoP_test_2.twbx.") I changed my data set to whole years to make checking easier in case you're wondering where 2024 data went.
Example 3 doesn't quite get there as the user should be able to select any duration of time period rather than week, quarter. But I've used some of your codes and am almost there I think but still struggling on the final calc so if you (or anybody else) can help me troubleshoot, it would be amazing:
All relevant calcs/parameters for this method are prefixed "TP2_" in attached workbook "PoP_test_2.twbx."
Here's what I have done:
- Created parameter TP2_Select Start Date and linked to calc TP2_startDate
- Created parameter TP2_Select End Date and linked to calc TP2_endDate
- In my example, I'm filtering by market and my chart is by market so I've tweaked code from your page to calculate Current Period Spend (which looks like it works nicely):
TP2_CurrentPeriodSpend =
{ FIXED [Market]:
sum(if [Date]>= [TP2_Select Start Date] and [Date]<= [TP2_Select End Date]
then [Spend]
end)}
- The final Current Period calc is to establish the number of days (so that we can know how many days our Prior Period should contain) NB I added 1 as I want to count all days in the range inclusive:
TP2_countCurrentDays =
DATEDIFF('day',max([TP2_Select Start Date]), max([TP2_Select End Date]))+1
- Then I started to work out what is necessary for the prior period. Firstly, the start date of the Prior Period. NB, this field is listed in the measure values rather than measure names section:
TP2_startDatePrevious =
DATE([TP2_Select Start Date])- [TP2_countCurrentDays]
- Then the end date of the Prior Period. Interestingly, unlike TP2_startDatePrevious, this field is placed in the measure names section (not measure values):
TP2_endDatePrevious =
DATE([TP2_Select Start Date])-1
- Then a cross-check to ensure the number of days in the prior date range matches the current date range
TP2_countPreviousDays =
DATEDIFF('day',[TP2_startDatePrevious], [TP2_endDatePrevious])+1
- The spend in the Prior Period and this is where it all falls apart. I have tried commenting out various parts and it seems this calc like the start date, but not the end date. I tried moving the end date to measure values and vice versa but couldn't get this to work. And perhaps my calculation is rubbish anyway... Here's where I have got to (it errors):
TP2_PreviousPeriodSpend =
{ FIXED [Market]:
sum( if [Date]>= [TP2_startDatePrevious] and [Date]<= [TP2_endDatePrevious]
then [Spend]
end)}
- I think it's TP2_endDatePrevious that is causing the problems because it's a dimension not a metric, but can't fix it.
- And the final calculation(s) would be for the percent change which is easy under normal circumstances, but I expect will trip me up...
Thanks for reading this far! Any genius suggestions?
