Small multiples - compare last 7 days this year versus last 7 days last year.

[ruthhacche]

If I select last 7 days in the date filter I can see Sales in the last year on one chart and a different measure (Sales LY) on a separate chart.  But I cannot see them on one chart with a shared axis (small multiples).

I can use a measure for max and min on the Y axis of both charts being the max and min of both measures but still have to use two charts.   Am I being thick?

[v-yangliu-msft · Accepted solution]

Hi  @ruthhacche ,

You can consider the following forms:

I created some data:

Here are the steps you can follow：

1. Create calculated table.

Date =
SUMMARIZE('Table','Table'[Date],'Table'[Amount])

2. Create measure.

Flag =
var _min=CALCULATE(MIN('Table'[Date]),ALLSELECTED('Table'))
var _max=CALCULATE(MAX('Table'[Date]),ALLSELECTED('Table'))
var _minlast=DATE(YEAR(_min)-1,MONTH(_min),DAY(_min))
var _maxlast=DATE(YEAR(_max)-1,MONTH(_max),DAY(_max))
return
IF(
    MAX('Date'[Date])>=_minlast&&MAX('Date'[Date])<=_maxlast,1,0)

3. The field of the first visual object is the field of Table

4. The field of the first visual object is the field of the Date table, and put [Flag] in the Filter, set is=1, and apply filter.

5. Result:

Show the selected dates of this year and last year to compare

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[amitchandak]

@ruthhacche , if last 7 days is selected in slicer or related data slicer then you can use measure like this for last year

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR('Date'[Date]))

or

Rolling 7 = CALCULATE(Average(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-7,DAY))

Rolling 7 last year = CALCULATE(Average(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAXX('Date', dateadd('Date'[Date ],-1, Year)) ,-7,DAY))

[ruthhacche]

I have the measures already - they work fine.  The question is how can I display them on one chart with one axis.  Small multiples only works if it is one measure with two fiscal years in the small multiple.

[amitchandak]

@ruthhacche , you need to use two measures in the same visual. Small multiple is for dimension not for measure

[ruthhacche]

You cannot always have two measures though can you.  eg a stacked column chart.  The below example would not take a second measure.  It does not matter - I will just stick to using two charts with common axes set by measures.

[amitchandak]

@ruthhacche , for that check if stacked clustered bar can help: https://www.defteam.com/stacked-clustered-bar-chart/