Calculate amount based on previous row in a matrix table

[S3]

Month		Amount		Previous Amount		Amount Difference
January		33				33
01		11				11
02		14		11		3
03		9		14		-5
February		28		25		3
01		20		9		11
02		6		20		-14
03		2		6		-4

Date		Amount
01.01.		11
02.01.		14
03.01.		9
01.02.		20
02.02.		6
03.02.		2

Index		Day Number		Month Name		Month Number		Date
01		01		January		01		01.01.
02		02		January		01		02.01.
03		03		January		01		03.01.
04		01		February		02		01.02.
05		02		February		02		02.02.
06		03		February		02		03.02.

Hello, 

I have a Matrix Table (desired outcome is the first table) showing a column for Months and their days as granularity, they are not a date table, as I don't want to use time intelligence. 
Their table is sorted by a column that's text, because I want it to be ordered like 01,02,03, 10,11 instead of 1,2,3,10,11 because only when it's text can it be ordered like that. 

I then have a column (previous amount) which I need to calculate in order to calculate the difference between each day. 

So what I would like to calculate is this:

- the previous row based on the index column of the dates (which is type text) 
important: to have the values of the previous amount add up in each total of the matrix table, and not just show the previous row when in the total. 

So when we don't expand the months, then the totals show for al column, also the total of the Previous Amount column. 

I would then subtract he previous amount from the amount to get the difference. 

Here is my previous column measure:

Previous Amount =  CALCULATE(
[Amount],FILTER(
ALLSELECTED('Table'),
[index]=MAX('Table'[Index])-1)
)

[v-xiaotang]

Hi @S3 

Thanks for reaching out to us.

I just want to confirm if you resolved this issue? If yes, you can accept the answer helpful as the solution or share you method and accept it as solution, thanks for your contribution to improve Power BI.

If you need more help, please let me know.

Best Regards,

Community Support Team _Tang

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

[lbendlin]

You're on the right track.  You say your index is a text column. That means you cannot do the -1 math on it.  You can also not use MAX() inside of CALCULATE()  unless you expect and want the context transition.

Not wanting to have a calendar table is not a good approach.  Learn to want to have a calendar table.

Please provide sanitized sample data that fully covers your issue. I can only help you with meaningful sample data.
Please paste the data into a table in your post or use one of the file services like OneDrive or Google Drive.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...

Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[S3]

Hello Ibendlin, 

Thank you for your suggestions, I inserted sample data and I made changes to my question in order to make it clearer. 

I would like the Previous Amount column to add up when it's not expanded, just like all other columns.
For clarification, I have a table called Calendar Table, but I didn't mark it as a date table.

[lbendlin]

You want the difference between each day, and you want to show that on month level .  What would be the meaning of that, and what would be the expected number say for January?

[lbendlin · Accepted solution]

Here is a proposal for an implementation. I think these formulas will be confusing for your users.

Measure Previous amount = 
var a = ADDCOLUMNS(VALUES(Dates[Date]),"p",var d = [Date] return calculate (max(Dates[Date]),Dates[Date]<d))
var b = ADDCOLUMNS(a,"v",var p=[p] return CALCULATE(sum(Amounts[Amount]),Dates[Date]=p))
return sumx(b,[v])

Measure Amount Difference = 
if(ISINSCOPE(Dates[Date]),sum(Amounts[Amount])-[Previous amount],
var a = max(Dates[MonthNo])
var b = CALCULATE(max(Dates[MonthNo]),ALL(Dates),Dates[MonthNo]<a)
return  sum(Amounts[Amount])-CALCULATE(sum(Amounts[Amount]),ALL(Dates),Dates[MonthNo]=b))

see attached

[S3]

Hello Ibendlin, 

Thank you so much, I will use your calculation in a normal table with the dates, because that way it works perfectly. However, with the Matrix table I was somehow hoping that there would be a way that when it's not expanding (showing only the months) then it will show also the previous value (in this case the value of the previous month) and also the difference between them (which was a high hope that this might be possible). 

Thanks again!