Rolling total of product quantity based on date slicer

[GabrielSantos]

I am struggling with this filter context interaction on a report I am developing. The relevant measures are as follows:

// This sets the current period start date to the first day in the selection.                                                         
var FirstDateInPeriod = CALCULATE ( MIN ( Dates[Date] ) ,
                                    ALLEXCEPT ( Dates, Dates[Date] ) )
                                    
// This sets the current period end date to the last day in the month.                                    
var LastDateInPeriod = MAX ( Dates[Date] ) 

// This defines the current period.
var CurrentRange = DATESBETWEEN ( Dates[Date], FirstDateInPeriod, LastDateInPeriod )     

// This is the invoice quantity of products sold in the current period and last period.                                                    
var ProductQTY = CALCULATE ( [Total QTY],
                               CALCULATETABLE( VALUES ( Sales[Product] ),
                                                 VALUES ( Sales[Product] ),
                                                    SAMEPERIODLASTYEAR ( CurrentRange ) ) )   

This measure determines whether a product was sold both in the current period (based on date slicer) and the previous period last year. It is working correctly.

I'm having an issue creating a rolling total of this measure. Here is what I have (not correct):

var QTYYTD = 

                                        
return  CALCULATE ( SUMX ( VALUES ( Dates[MonthAndYearAbbr] ),
                                    ProductQTY ),
                            CurrentRange )      

I know what is happening here. The measure is correctly evaluating the 'ProductQTY' measure, but the outer calculate evaluates the number of months in the selected period and iterates over values of the month as defined by the slicer.

Here is a visual of what is happening:

 

The 'ProductQTY' count is correct. As you can see, the VALUES ( MonthAndYearAbbr ) returns three months for Jul 2017 because the slicer passes the 5/30/2017 as the starting date in the range (CurrentPeriod). The three months are each given a value of 7, and SUMX returns 21. 

The desired measure would display the following:

Jul 2017: 7

Aug 2017: 7

Sep 2017: 7

Oct 2017: 13

Any help is appreciated!

[v-yulgu-msft]

Hi @GabrielSantos,

Could you please share some sample data from 'sales' table and 'dates' table?

Regards,

Yuliana Gu