Adding column to show last month's value

[agy2]

Hi everyone,

I am currently using a table similar to the one below, but with significantly more rows (data going back 18 months for several thousand accounts). Rows are added for each account at the end of each month with an end of month value for X.

AccountID	Date	X
1	31/08/2017	0
2	31/08/2017	0
3	31/08/2017	0
4	31/08/2017	0
1	30/09/2017	0
2	30/09/2017	2
3	30/09/2017	3
4	30/09/2017	1
1	31/10/2017	1
2	31/10/2017	2
3	31/10/2017	2
4	31/10/2017	2
5	31/10/2017	0

What I am trying to create is a column as in the table below, which shows the value of X for each account at the end of the previous month, however cannot get the correct value. If I simply calculate X for the previous month, blank values are returned as there is nothing in my calculation to tell Power BI to link the rows by account ID. Any help would be great, thanks!

AccountID	Date	X	Previous X
1	31/08/2017	0
2	31/08/2017	0
3	31/08/2017	0
4	31/08/2017	0
1	30/09/2017	0	0
2	30/09/2017	2	0
3	30/09/2017	3	0
4	30/09/2017	1	0
1	31/10/2017	1	0
2	31/10/2017	2	2
3	31/10/2017	2	3
4	31/10/2017	2	1
5	31/10/2017	0

[Zubair_Muhammad · Accepted solution]

Hi @agy2

Try this calculated Column

Previous X =
CALCULATE (
    VALUES ( Table1[X] ),
    ALLEXCEPT ( Table1, Table1[AccountID] ),
    PREVIOUSMONTH ( Table1[Date] )
)

[agy2]

Hi @Zubair_Muhammad,

This works perfectly, thanks for your help!