Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Good afternoon,
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:
Solved! Go to Solution.
You may try this formula logic to calculate last 4 years
Last3year =
CALCULATE (
[Your current value measure],
FILTER (
ALL ( 'date' ),
'date'[Year] = MAX ( 'date'[Year] ) - 3
&& 'date'[Week Number] = MAX ( 'date'[Week Number] )
)
)
and you could use the same logic for last year/last 2years/last 4years measure.
and it couldn't be put into column chart, since there is a field in 'Lenged' value of the visual, it couldn't put any measure in it again.
You could put it into matrix visual instead.
If not your case, please share a simple sample pbix file and your expected output. that will be great help.
Regards,
Lin
You may try this formula logic to calculate last 4 years
Last3year =
CALCULATE (
[Your current value measure],
FILTER (
ALL ( 'date' ),
'date'[Year] = MAX ( 'date'[Year] ) - 3
&& 'date'[Week Number] = MAX ( 'date'[Week Number] )
)
)
and you could use the same logic for last year/last 2years/last 4years measure.
and it couldn't be put into column chart, since there is a field in 'Lenged' value of the visual, it couldn't put any measure in it again.
You could put it into matrix visual instead.
If not your case, please share a simple sample pbix file and your expected output. that will be great help.
Regards,
Lin
Hi @v-lili6-msft ,
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.
Kind regards
Karl
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |