Rolling Last Twelve Months Cumulative Bar Graph

[Anonymous]

I want to create a bar graph that shows the monthly LTM total of sales. So, for example, a bar for July with sales summed from July 2019-August 2018, a Bar of June with sales summed from June 2019 to July 2018, and so on until whatever time period I choose. Say for example the last bar is September 2017, that bar is the sum of sales from September 2017-October 2016. There is nothing with a cumulative monthly LTM bar graph anywhere, please help!!

[jdbuchanan71 · Accepted solution]

Hello @Anonymous 

This measure assumes you have a Calendar table linked to your data and a measure that sums sales

R12M Sales =
CALCULATE (
    [Sales],
    DATESINPERIOD ( Calendar[Date], MAX ( Calendar[Date] ), -11, MONTH )
)

[jdbuchanan71 · Accepted solution]

Hello @Anonymous 

This measure assumes you have a Calendar table linked to your data and a measure that sums sales

R12M Sales =
CALCULATE (
    [Sales],
    DATESINPERIOD ( Calendar[Date], MAX ( Calendar[Date] ), -11, MONTH )
)

[Anonymous]

Thanks for the quick reply, I have a date linked to each sale. Is the calendar date this date or an independent calendar I bring in to a separate table?

[jdbuchanan71 · Accepted solution]

It will be a seperte table that you have in the model.  You can use New Table > 

Dates = 
VAR DateRange = CALENDARAUTO()

RETURN
ADDCOLUMNS(
    DateRange,
    "Year",YEAR ( [Date] ),
    "Month", FORMAT ( [Date], "mmmm" ),
    "MonthNum", MONTH ( [Date] ),
    "Month Year", FORMAT ( [Date], "mmm-yyyy"),
    "MonthYearNum", YEAR ( [Date] ) * 100 + MONTH ( [Date] ),
    "Quarter Year", "Q" & FORMAT ( [Date], "q-yyyy" ),
    "QtrYearNum", YEAR ( [Date] ) * 100 + VALUE ( FORMAT ( [Date], "q" ) )
)

Then link the [Date] column of your data to the Dates[Date] column.

[Anonymous]

You are a life saver. Going through your process has helped me learn so much about using dates and filtering. Thank you!