Forum Discussion
Previous Record
With help from dearwatson i was able to identify the status changes between records
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 |
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-fiscal-attributes-using-power-query/
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] ) )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,
2 Replies
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-fiscal-attributes-using-power-query/
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] ) )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,