How can I put data on a Bar Chart for a measure for the selected date range and prior 3 years

[Anonymous]

Can someone tell me how to do this - I need to put data in a bar chart for the current selected period and that same period in the last 3 years. For example I have a timeline slicer that shows the months in my Date table:

So for the selected range Jan - Apr 2020 I would like to plot the Sum of a Volume measure as well as the sum of that same measure for Jan - Apr 2019, Jan - Apr 2018, and Jan - Apr 2017

Any ideas on how to get a Sum for the selected period for previous periods?

[Anonymous]

In your bar chart:

Put in the "Legend" field [Date].[Year]. 

Put in the "X Axis" field [Date].[Month].

Put in the "Value" field the sum of volume.

[Anonymous]

This doesnt appear to work. I have this measure

Total Volume =

CALCULATE(

SUM(FactVolume[Volume]),

ALL('Date'[Date])

)

My timeline slicer shows Jan-Apr 2020 so I am not getting previous years

[Mariusz]

Hi @Anonymous 

You can create two extra measures as below.

Last Year Measure

CALCULATE(
    [Original Measure],
    SAMEPERIODLASTYEAR( 'calendar'[Date] )  
)

Year Before last year Measure

CALCULATE(
    [Last Year Measure],
    SAMEPERIODLASTYEAR( 'calendar'[Date] )  
)

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Anonymous]

Using the LastYearMeasure technique gives me this

I would like to see 2017, 2018, 2019, and 2020 on the x-axis with one bar for each year. Any ideas?

[Anonymous]

Anyone have any ideas on this. It is taking a dynamic continuous selection of months (i.e. Feb 2020 to Apr 2020) in a slicer, Getting the Sum of a measure for that time period, and also getting that sum for that same time period for the past 3 years. And then plot them on a bar chart that shows the year for each on the axis (ie.e 2017, 2018, 2019, 2020) and the associated measure on the bar.

[v-easonf-msft · Accepted solution]

Hi , @Anonymous 

Here is a demo I made( hope my idea  will be helpful to you )
pbix attach 

I suggest your to create a calculate table and  extract the fields of the month separately.

Then  using the "month" field instead of the "date" field to establish a relationship between two tables

It will  shows as below :

Best Regards,
Community Support Team _ Eason

[Anonymous]

Just a follow-up if you know this:

The solution requires a relationship between Table and Sales on the Month field. What if there were mutiple relationships (i.e. a relationship on Date field..or a DateKey field, etc) where the other Date relationship is the "Active" relationship. For example in my Date table I have DateKey and that is the Active relationship to the Fact table in my model. In my Date table I have a field called MonthOfYear and I havee added an INACTIVE relationship between that field and the Fact table month column

If I create a measure like this it does NOT seem to work:

Total Volume Selected Period =

CALCULATE(

SUM(FactVolume[Volume]),

USERELATIONSHIP(FactVolume[Month],'Date'[MonthOfYear])

)

It only works if the relationship between months is Active. Any ideas?

[Anonymous]

Perfect. It works. Thanks so much