Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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] ) )
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,
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,
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] ) )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |