3 months rolling based on the Year and Month Slicer selection

[srivally]

Hello all,

I am working on 3 Months rolling data in a line chart visualization.
For example, if I select 2006 year then I should get cumulative total for last 3 months(i.e, October, November, December).
If I select 2006 year and November month, I should get September, October, November cumulative Sales.

Here I have created a measure, which is getting last 3 Month Sales total. But I want as cumulative of last 3 Sales for each month.

Reference Data:
Year   Month   Sales  Rolling
2006  Sep        10      10
2006  Oct         30      40
2006  Nov       30       70

I have attached my .pbix file in the  following link: https://www.dropbox.com/s/6o0sy2e2ruuxl4e/Rolling.pbix?dl=0
Please help me on this.

[v-frfei-msft]

Hi @srivally,

Please refer to the steps as below.

1. Create a new calendar table in your PBIX and create required columns (Year,MON,YearMonth,Running Total1) in this new table.

calendar table = 
ADDCOLUMNS (
    FILTER (
        CALENDAR ( DATE(2004,01,01), DATE(2013,12,31)),
        DAY ( [Date] ) = 1
    ),
    "Amount", CALCULATE (
        SUM ( Sales[Net Sales] ),
        YEAR(Sales[Date.Date])=YEAR(EARLIER([Date]))
            && MONTH(Sales[Date.Date]) = MONTH ( EARLIER ( [Date] ) )
    )
)

Running Total1 = CALCULATE(SUM('calendar table'[Amount]),DATESINPERIOD('calendar table'[Date],LASTDATE('calendar table'[Date]),-3,MONTH))

2. Create the measures as below.

running total of selected yearmonth = SUM('calendar table'[Running Total1])

running total of whole year = CALCULATE(SUM('calendar table'[Running Total1]),FILTER('calendar table','calendar table'[YEAR] in {MIN('calendar table'[YEAR]),MAX('calendar table'[YEAR])}&& 'calendar table'[MON]="December"))

Measure = if(ISFILTERED('calendar table'[YEAR])&&ISFILTERED('calendar table'[MON]),[running total of selected yearmonth],IF(ISFILTERED('calendar table'[YEAR]),[running total of whole year]))

3. Then we can get the result as below.

For more details, please check the pbix as attached.

https://www.dropbox.com/s/n5bt0uzab1c3qwk/3%20months%20.pbix?dl=0

Regards,
Frank

[srivally]

Hi @v-frfei-msft

Here, we are getting the result like, if we select July month I'm getting the cumulative total of May,June July and displaying only July Month cumulative total. But I want rolling to display as for May it should display MaySales, for June Month cumulative of May and June, for July it should display cumulative of May, June and July by selecting only July Month. 
If I do not select any month then it should display Oct month Sales, for Nov cumulative of Oct and Nov Sales, for Dec cumulative of Oct,Nov and Dec.
And here I should select only single month and it should display 3 Months.

Regards,

Srivalli