Rolling 12 Month calculation error (using CALCULATE and DATESINPERIOD) using Excel's Power Pivot

Perturbed — Wed, 10 Aug 2022 10:06:09 GMT

Hello All,
Thanks for taking the time to help me.
I am trying to analyse my passive income using Excel's Power Pivot and I haven't been able to create a measure for "Income 12 Month Rolling Total".
Here's a Link to my sample Excel Spreadsheet in OneDrive@ https://1drv.ms/x/s!AjEQJC3O4N83iIQm2mI3-Yo-Kn2D4w?e=TiemTV

The source data I created by merging data (on date and income type) from a Net Worth report with data from a CashFlow report to create the (sample) source data as shown here.

ValueDate Incometype NetWorthValue Income

31/07/2021	Interest	186,285.27	198.63
31/07/2021	Pension	303,571.43	2,752.05
31/07/2021	Dividends	564,213.92	4,589.82
31/08/2021	Interest	186,132.84	432.91
31/08/2021	Dividends	576,622.91	0.00
31/08/2021	Pension	303,571.43	2,356.30
30/09/2021	Dividends	564,946.69	663.62
30/09/2021	Pension	444,285.71	3,467.19
30/09/2021	Interest	43,331.39	222.14
31/10/2021	Interest	49,353.25	101.69
31/10/2021	Pension	444,285.71	3,862.95
31/10/2021	Dividends	577,708.34	8,780.32
30/11/2021	Pension	444,285.71	3,467.19
30/11/2021	Interest	88,953.99	144.13
30/11/2021	Dividends	557,372.59	41.41
31/12/2021	Pension	444,285.71	3,467.19
31/12/2021	Interest	89,188.07	204.33
31/12/2021	Dividends	585,460.36	0.00
31/01/2022	Dividends	581,974.86	3,192.36
31/01/2022	Pension	444,285.71	3,862.95
31/01/2022	Interest	92,957.30	224.65
28/02/2022	Pension	444,285.71	3,467.19
28/02/2022	Interest	92,043.12	211.81
28/02/2022	Dividends	600,116.33	6.45
31/03/2022	Pension	446,428.57	3,455.22
31/03/2022	Dividends	607,747.10	75.68
31/03/2022	Interest	86,767.79	224.20
30/04/2022	Pension	446,428.57	3,873.01
30/04/2022	Dividends	589,216.81	4,321.29
30/04/2022	Interest	91,105.63	242.58
31/05/2022	Interest	90,145.84	256.05
31/05/2022	Dividends	586,059.53	101.83
31/05/2022	Pension	446,428.57	3,514.11
30/06/2022	Pension	446,428.57	3,542.73
30/06/2022	Interest	76,212.84	249.95
30/06/2022	Dividends	554,106.98	51.48
31/07/2022	Pension	446,428.57	3,958.27
31/07/2022	Interest	82,027.54	321.14
31/07/2022	Dividends	550,863.35	5,406.45

I loaded this table into the Excel Data Model. Then in the data model I created a date table ('Calendar') and added a relationship between the 'Calendar'[Date] and the Source Data[ValueDate].
"tblNWYieldbyDateByType [ValueDate] Many to One (*:1) << To tblNWYieldbyDateByType Calendar [Date]"

Then I created a new measure with the following DAX code.

12MonthIncome=CALCULATE(Sum([Income]),
(DATESINPERIOD('Calendar'[Date],
MAX('Calendar'[Date]),
-12, MONTH)))

Here is my sample Pivot Table showing that my 12MonthIncome measure;
1) Isn't creating a running total, only includes the value for current row.
2) Only reflects values from the current year. I don't want the running total to reset on Calendar changes like year-end.

(Apologies for the presentation. My copy-and-paste didn't work as desired. I have included a link to the Excel Spreadsheet near the top of this posting). The columns are date and then for each Income Type (Dividends, Interest, Pension) there are values for "Sum of Income" and "12 Month Income".

Column Labels Dividends Interest Pension Total Sum of IncomeTotal 12MonthIncomeDateSum of Income12MonthIncomeSum of Income12MonthIncomeSum of Income12MonthIncome Grand Total35,37913,1563,8391,73064,26725,673103,48540,559

30/11/2020	4		114		2,284		2,402
31/12/2020	505		118		2,284		2,908
31/01/2021	2,600		123		2,661		5,385
28/02/2021	18		87		2,284		2,389
31/03/2021	0		79		2,284		2,363
30/04/2021	4,931		83		2,709		7,724
31/05/2021	88		86		2,356		2,530
30/06/2021	2		113		2,356		2,472
31/07/2021	4,590		199		2,752		7,541
31/08/2021	0		433		2,356		2,789
30/09/2021	664		222		3,467		4,353
31/10/2021	8,780		102		3,863		12,745
30/11/2021	41		144		3,467		3,653
31/12/2021	0		204		3,467		3,672
31/01/2022	3,192	3,192	225	225	3,863	3,863	7,280	7,280
28/02/2022	6	6	212	212	3,467	3,467	3,685	3,685
31/03/2022	76	76	224	224	3,455	3,455	3,755	3,755
30/04/2022	4,321	4,321	243	243	3,873	3,873	8,437	8,437
31/05/2022	102	102	256	256	3,514	3,514	3,872	3,872
30/06/2022	51	51	250	250	3,543	3,543	3,844	3,844
31/07/2022	5,406	5,406	321	321	3,958	3,958	9,686	9,686

Here is a mock-up of one sample Pivot Table I'm trying to achieve.-



30/11/2020	4		114		2,284		2,402
31/12/2020	505		118		2,284		2,908
31/01/2021	2,600		123		2,661		5,385
28/02/2021	18		87		2,284		2,389
31/03/2021	0		79		2,284		2,363
30/04/2021	4,931		83		2,709		7,724
31/05/2021	88		86		2,356		2,530
30/06/2021	2		113		2,356		2,472
31/07/2021	4,590		199		2,752		7,541
31/08/2021	0		433		2,356		2,789
30/09/2021	664		222		3,467		4,353
31/10/2021	8,780		102		3,863		12,745
30/11/2021	41		144		3,467		3,653
31/12/2021	0		204		3,467		3,672
31/01/2022	3,192	3,192	225	225	3,863	3,863	7,280	7,280
28/02/2022	6	6	212	212	3,467	3,467	3,685	3,685
31/03/2022	76	76	224	224	3,455	3,455	3,755	3,755
30/04/2022	4,321	4,321	243	243	3,873	3,873	8,437	8,437
31/05/2022	102	102	256	256	3,514	3,514	3,872	3,872
30/06/2022	51	51	250	250	3,543	3,543	3,844	3,844
31/07/2022	5,406	5,406	321	321	3,958	3,958	9,686	9,686

Re: Rolling 12 Month calculation error (using CALCULATE and DATESINPERIOD) using Excel's Power Pivot

v-jianboli-msft — Wed, 17 Aug 2022 08:38:02 GMT

Hi Perturbed ,

Based on your sample, I have changed the measure.

Please try:

12MonthIncome = VAR _a = MAX ( 'tblNWYieldbyDateByType'[ValueDate] ) VAR _b = CALCULATE ( SUM ( [Income] ), FILTER ( ALLEXCEPT ( 'tblNWYieldbyDateByType', tblNWYieldbyDateByType[Incometype] ), [ValueDate] >= DATE ( 2020, 11, 1 ) && [ValueDate] <= _a && [ValueDate] >= EOMONTH ( _a, -12 ) ) ) RETURN IF ( DATEDIFF ( DATE ( 2020, 11, 30 ), _a, MONTH ) >= 11, _b )

Output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Rolling 12 Month calculation error (using CALCULATE and DATESINPERIOD) using Excel's Power Pivot

Perturbed — Thu, 18 Aug 2022 08:32:22 GMT

Thanks very much Jianbo Li,

This is the help I was looking for: The "tips & tricks" that really helped were

- Use the "AllExcept" function to prevent filtering on the field, [IncomeType], that I want to summarise on.

- Use simple date comparisons rather than the built-in Time Period function DATESBETWEEN

So as not to lose the point of where I was struggling, I copied and pasted only the last portion of my data. I had to tweak your code to go all the way back to my first records that were filtered out. Here's the code I'm going to use to include all of my data

VAR EndDate = MAX ( tblNWYieldbyDateByType[ValueDate] ) /* Until the end of my records */ VAR StartDate = DATE( 2009, 01,01) /* Any Date before my first record */ VAR IncomeSUM = CALCULATE ( SUM(tblNWYieldbyDateByType[Income] ), FILTER( ALLEXCEPT ( tblNWYieldbyDateByType,tblNWYieldbyDateByType[Incometype] ) , [ValueDate] >= StartDate && [ValueDate] <= EndDate && [ValueDate] >= EOMONTH ( EndDate, -11 ) ) ) /* End of Calculate function */ RETURN IF ( DATEDIFF ( StartDate, EndDate, MONTH) >= 11, IncomeSUM)

Thank you😊

topic Rolling 12 Month calculation error (using CALCULATE and DATESINPERIOD) using Excel's Power Pivot in DAX Commands and Tips

Rolling 12 Month calculation error (using CALCULATE and DATESINPERIOD) using Excel's Power Pivot

Re: Rolling 12 Month calculation error (using CALCULATE and DATESINPERIOD) using Excel's Power Pivot

Re: Rolling 12 Month calculation error (using CALCULATE and DATESINPERIOD) using Excel's Power Pivot