Cummulative sum based on slicer selection

[Anonymous]

Hi Team,

Currently We've developed a visual showing that last 6 months of salesamount based on slicer(Month-Year) selection.

Problem Statement : Need to calculate dynamic cummulative sum of the last 6 months based on the slicer selection

Attached is the PBI Ref link : https://1drv.ms/u/s!Au-aOkl1BoHuhxIPCmu25tKY_HLm?e=dFRnwZ

For Ex:1 if we select dec-2017 then will showcase last 6 months(Dec-2017,nov-2017,oct-2017,sep-2017,aug-2017,july-2017)

   Ex2:if we select Aug-2018 then will showcase last 6 months(Aug-2018,July-2018,June-2018,May-2018,April-2019,Mar-2018)

Current OutPut

Expected OutPut:

Power BI Reference Link:

https://1drv.ms/u/s!Au-aOkl1BoHuhxIPCmu25tKY_HLm?e=dFRnwZ

Thanks In Advance

[Vera_33 · Accepted solution]

Hi @Anonymous 

Here is one way

Sales (last n months) 1 = 
VAR MaxFactDate =
   CALCULATE ( MAX ( Sales[Date] ), ALL ( 'Date' ) ) -- ignore the selected date filter, and find the max of date in Sales table 
VAR FDate =
    ENDOFMONTH ( 'Date'[Date] ) -- get the last day of the month selected in the date filter
VAR Edate =
    EDATE ( FDate, -6 ) -- get the last day of -N months
RETURN
    IF(MaxFactDate<=FDate,
  SUMX(FILTER(ALL('Sales'),'Sales'[Date]<=MaxFactDate&&'Sales'[Date]>=Edate),Sales[Sales]))