cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ims20180411kjb
Frequent Visitor

Showing Previous Year Totals in a Column Chart

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.

 Admissions Dashboard.png

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] )
RETURN
IF(
( SiteVisitsTableYear < DatesTableYear) &&
( SiteVisitsTableYear >= DatesTableL3Year) &&
( SiteVisitsTableWeek = DatesTableWeek ) ,
-- Flag if true --
1
)
 
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.
 
Regards
 
Karl
 
 
1 ACCEPTED SOLUTION
v-lili6-msft
Community Support
Community Support

hi @ims20180411kjb 

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-lili6-msft
Community Support
Community Support

hi @ims20180411kjb 

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi 

 

 

 

 

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

Check it Out!

Mark your calendars and join us on Thursday, September 29 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors