Rolling 12 month sum for completed months

[Chinoos]

I'm struggling to get rolling 12 months for the months are completed. For example; For the current month the value should display Zero and for the completed month it should display rolling 12 months values. For 2016-Dec the value=sum(2016-Jan till 2016-Dec), 2016-Nov=sum(2015-Dec till 2016-Nov)

Year	Month	Value
2016	8	985193929.9
2016	9	985428542.2
2016	10	985365989.1
2016	11	992519096.7
2016	12	980109375.8
2017	1	0

Please suggest any idea, if you have come across the same

[Vvelarde · Accepted solution]

@Chinoos

you can check if a month is complete with: (Bold Part)

ValueCalc = if(TODAY()>=EOMONTH(VALUES(Table1[DateRef]);0);EXPRTRUE;EXPRFALSE)

[v-shex-msft · Accepted solution]

Hi @Chinoos,

According to your description, you can refer to below formulas if it works on your side.

1. Calculate the total value from previous date to current date.

Total = 
var minDate= DATE(YEAR(MAX([Date])),MONTH(MAX([Date]))-11,DAY(MAX([Date])))
return 
SUMX(FILTER(ALL('Table'),[Date]>= minDate&&[Date]<=MAX([Date])),[Amount])

2. Calculate the rolling total in current year.

Rolling total = SUMX(FILTER(ALL('Table'),[Date]<=MAX([Date])&&[Date].[Year]=MAX([Date].[Year])),[Total])

If above is not help, please share some sample data to test.

Regards,

Xiaoxin Sheng

[Chinoos · Accepted solution]

Thanks the formulas, it worked for rolling 12 months sum, but i had mades some changes since date and measure are coming from 2 different tables. I'm using just one formula to get the result.

Rolling 12 months =
var minDate= DATE(YEAR(MAX('Fiscal Calendar Date'[Fiscal Calendar Date])),MONTH(MAX('Fiscal Calendar Date'[Fiscal Calendar Date]))-12,DAY(MAX('Fiscal Calendar Date'[Fiscal Calendar Date])))
return
CALCULATE( SUM ( 'Sales'[Gross Sales] ),FILTER(ALL('Fiscal Calendar Date'),'Fiscal Calendar Date'[Fiscal Calendar Date]> minDate&&'Fiscal Calendar Date'[Fiscal Calendar Date]<=MAX('Fiscal Calendar Date'[Fiscal Calendar Date])))

Please find the sample date below( from the Fiscal Calendar Date Hierarchy, I'm dsiplaying only the Year and Month)

Year	Month	Gross
2015	January	55039455.96
2015	February	49964844.34
2015	March	67875264.74
2015	April	85248808.27
2015	May	88028313.78
2015	June	97662930.16
2015	July	93672919.35
2015	August	89597379.02
2015	September	93686553.51
2015	October	97872548.47
2015	November	81438568.72
2015	December	77867879.91
2016	January	55328963.33
2016	February	61547754.60
2016	March	78241869.13
2016	April	81956103.71
2016	May	89760083.71
2016	June	96347579.89
2016	July	83884792.87
2016	August	97057881.05
2016	September	95230929.74
2016	October	90063862.46
2016	November	90997731.83
2016	December	59691823.46