The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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
|
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
|
Here is a mock-up of one sample Pivot Table I'm trying to achieve.-
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
|
Solved! Go to Solution.
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.
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.
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😊
User | Count |
---|---|
16 | |
8 | |
7 | |
6 | |
6 |
User | Count |
---|---|
26 | |
13 | |
12 | |
8 | |
8 |