Previous Record

[Sir_night]

With help from dearwatson i was able to identify the status changes between records 

https://community.powerbi.com/t5/Desktop/Combining-Dynamic-Query-Parameters-and-Append-Queries/m-p/2...

But i still need to be able to identify the previos record

my data looks like this , but how do i creat the prevouse record column 

Date	Ref	status	index	status change	prevouse  record
1/02/2017	1	A	0	0	0
2/02/2017	1	A	1	0	1
3/02/2017	1	O	2	1	1
4/02/2017	1	G	3	1	0
5/02/2017	1	G	4	0	0
6/02/2017	1	G	5	0	0

[GilbertQ · Accepted solution]

Hi @Sir_night

You could create the following measure as long as you have got a Date table, which is required for the measure below.

To create a Date table you can follow this blog post: https://www.fourmoo.com/2016/09/13/power-bi-how-to-easily-create-dynamic-date-tabledimension-with-fi...

And then once that is done you can then create the following measure.

Measure Yesterday Rolling = 
CALCULATE (
    [Measure Name],
    LASTNONBLANK ( DATEADD ( 'Date'[Date], -1, DAY ), [Measure Name] )
)

[v-sihou-msft · Accepted solution]

@Sir_night

In your scenario, since you already have an index column, if you want to calculate previous record, you can create a calculated column with EARLIER to get the corresponding one:

previous record =
CALCULATE (
    MAX ( Table[status change] ),
    FILTER ( Table, Table[index] = EARLIER ( Table[Index] ) - 1 )
)

However, based on your data, it seems your "prevouse record" shows the next "status change", so your column might be:

=
CALCULATE (
    MAX ( Table[status change] ),
    FILTER ( Table, Table[index] = EARLIER ( Table[Index] ) + 1 )
)

Regards,

[GilbertQ · Accepted solution]

Hi @Sir_night

You could create the following measure as long as you have got a Date table, which is required for the measure below.

To create a Date table you can follow this blog post: https://www.fourmoo.com/2016/09/13/power-bi-how-to-easily-create-dynamic-date-tabledimension-with-fi...

And then once that is done you can then create the following measure.

Measure Yesterday Rolling = 
CALCULATE (
    [Measure Name],
    LASTNONBLANK ( DATEADD ( 'Date'[Date], -1, DAY ), [Measure Name] )
)