I was hoping to receive advice on how to include totals, summarising the performance of previous periods (last week/this week last year), in a report controlled by date slicers.
The report currently summarises the visit behaviour of members based on the selected year and week number slicer values.
As the screenshot below shows, club admission figures for a week are broken down by day and session (aft/eve) and in the case of the 'Session Admissions' chart, also shows equivalent figures for last week and the same week, last year, together with a set of fixed benchmark figures that display values for both 'bad' and 'good' attendance figures in that specific session.
Now on the whole, this report functions perfectly. However, I have had to 'fix' the 'Previous Week Admissions' matrix and associated column chart in the middle-right section of the report so that it always shows the weekly totals for the last 4 years (from the current year) regardless of the year value selected in the slicer.
Ideally, these objects would also dynamically respond to the slicer selections, e.g. if the slicer year was chosen as 2018 and the slicer week as 45, then the 'Previous Week Admissions' matrix and chart would summarise the total weekly member admissions figures for week 45 in 2018 (in the chart, not the matrix), 2017, 2016 & 2015.
At the moment, these slicer selections would not alter either of these visuals and they would remain as shown in the screenshot. N.B. they are dynamic enough to always show the current year totals plus the last 4 years so in 2020, totals would be shown for 2020 back to 2017.
The data model is quite straightforward and consists of a table of distinct Members together with an accompanying Site Visits (fact table) table that records all club admissions by date (visitID, visitDate, visitClub & MemberID).
I have also created a date reference table which is linked to the Site Visits and from which the slicers currently operate.
I have attempted several different solutions, one of which succeeded, by creating a new isolated year table (year, week number) but NOT linking it to the Site Visits table and then creating the following filter measure to limit years shown in the chart and table visiuals to the last 3 years:
L3Y Filter =
VAR SiteVisitsTableYear = MIN( 'Site Visits'[Visit Year] )
VAR SiteVisitsTableWeek = MIN( 'Site Visits'[Visit Week] )
VAR DatesTableYear = MIN( 'Sites Year Slicer'[Year] )
VAR DatesTableL3Year = DatesTableYear - 3
VAR DatesTableWeek = MIN( 'Sites Year Slicer'[Week Number] )
( SiteVisitsTableYear < DatesTableYear) &&
( SiteVisitsTableYear >= DatesTableL3Year) &&
( SiteVisitsTableWeek = DatesTableWeek ) ,
-- Flag if true --
However, if I attempt to use the above logic for the rest of the report, especially the 'Session Admissions' chart containing the totals for this week, last week and this week last year, then I run into all sorts of problems.
I appreciate this is a very lengthy post but I have spent several days looking into this to no avail so any input or advice would be greatly appreciated.
thank you very much for your response. I have tested your suggestion successfully and whilst I'm unable to include any elements other than the measures in a column chart, I have been to overlay additional 'title' measures that allow me to dynamically label each of the columns within the chart and accompanying matrix:
So thank you very much, your reply was very helpful and much appreciated.