Wrong totals in matrix. Need help!

[Anonymous]

Hi!

I have the following table:

Date	Total Balance	Total Balance Last Month	One Month Change
feb.16	8,6	6,7	1,9
mar.16	8,2	8,6	-0,4
apr.16	7,9	8,2	-0,3
mai.16	8	7,9	0,1
jun.16	8,7	8	0,7
jul.16	9	8,7	0,3
aug.16	9,2	9	0,2
sep.16	8,4	9,2	-0,8
okt.16	8,6	8,4	0,2
nov.16	8	8,6	-0,6
des.16	7	8	-1
jan.17	6,7	7	-0,3
feb.17	6,2	6,7	-0,5
mar.17	6,5	6,2	0,3
SUM	111	WRONG SUM	= 111 - WRONG SUM

Balance last month is calculated like below because i miss data from all months of January and need to skip it so the balance from December shows up as the last month balance for February.:

Total Balance LM =
IF(
   CALCULATE([Total Balance]; DATEADD(Dates[Date];-1;MONTH))=0;
       CALCULATE([Total Balance]; DATEADD(Dates[Date];-2;MONTH));
       CALCULATE([Total Total Balance]; DATEADD(Dates[Date]; -1; MONTH)))

Total Balance = SUM(Balance)

My problem is that the summarization of the Total Balance Last Month column is wrong in the matrix, and therefore also the One Month Change column.. Making it impossible to show aggregated change within certain periods etc... I have no clue why! (The total is only wrong when i slice the matrix by year/month etc)

Could somebody help me sort this out?

Thank you!

[v-piga-msft]

Hi @Anonymous,

From your description, if I understand your scenario correctly that your problem is the total only shows wrong when you slice the matrix by year/month. I made a test with the data you provided and I cannot reproduce the issue as yours.

My test result is below.

If my result is not you want, could you show me your data sample which can reproduce the scenario and your excepted output. You can upload it to OneDrive or Dropbox and post the link here. (Do mask sensitive data before uploading.)

In addition, you could refer to my test pbix file.

Best Regards,

Cherry

[Anonymous]

@@v-piga-msf Thank you for the response!

 Hi, i have found the problem now; the problem is that my Date Table runs longer than Mar 17. So there is a "virtual row" below Mar 17 which gets calculated like there is 0 in the Total Balance Column, 6,5 in the Total Balance Last Month Columns and 0 - 6,5 = -6,5 in the One Month Change Column  (ref formulas 1st post)  

I have fixed this by makin my date table correnspond to my fact table, but this will be a pain when I update the spreadsheet.

@v-piga-msft do you know the relevant DAX to make the summarization stop at the last date in my fact table as opposed to the last date in my date table?

Thank you!