Forum Discussion
How to get previous record in Power Bi
I have a table like below which shows the Invoice amount at various dates. The invoice amount would remain the same between two dates. For ex, amount is 600 on 5-1-2021 and would remain the same till 5-8-2021, at which point it changes to 500. If I want to view the invoice amount for any intermediate date (say 5-13-2021, amount = 500) how do I go about doing this in Power BI. Thanks.
| Date | Invoice Number | Amount |
| 5-1-2021 | 1234 | 600 |
| 5-8-2021 | 1234 | 500 |
| 5-15-2021 | 1234 | 400 |
| 5-22-2021 | 1234 | 300 |
| 5-29-2021 | 1234 | 200 |
| 6-5-2021 | 1234 | 100 |
Anonymous , Try a measure like
Measure =
VAR __sel = maxx(allselected('Date'), 'Date'[Date])
VAR __id = MAX ('Table'[Invoice Number] )
VAR __date = CALCULATE ( MAXX(filter('Table', [Date] <__sel),'Table'[Date] ), ALLSELECTED ('Table' ), 'Table'[Invoice Number] = __id )
CALCULATE ( max ('Table'[CHANNEL] ), VALUES ('Table'[Invoice Number] ),'Table'[Invoice Number] = __id,'Table'[Date] = __date )
5 Replies
- amitchandak
Super User
Anonymous , Try a measure like
Measure =
VAR __sel = maxx(allselected('Date'), 'Date'[Date])
VAR __id = MAX ('Table'[Invoice Number] )
VAR __date = CALCULATE ( MAXX(filter('Table', [Date] <__sel),'Table'[Date] ), ALLSELECTED ('Table' ), 'Table'[Invoice Number] = __id )
CALCULATE ( max ('Table'[CHANNEL] ), VALUES ('Table'[Invoice Number] ),'Table'[Invoice Number] = __id,'Table'[Date] = __date ) Thanks amitchandak , it works peacefully.
The measure works fine for a single and multiple invoice. But how should we arrive at total amount for a set of selected invoices or all invoices put together. Thanks.
Ratpay Invoice Date 600 1234 5/1/2021 500 1234 5/8/2021 400 1234 5/15/2021 300 1234 5/22/2021 200 1234 5/29/2021 100 1234 6/5/2021 350 5678 5/3/2021 250 5678 5/10/2021 150 5678 5/17/2021 50 5678 5/24/2021