Data Transform/visualization help

[MrToast20]

Hi all,

I'm new to powerbi but enjoying it so far.

I have some data in the format below. I'm trying to establish trends/patterns in this data

The RefNo can have multiple rows of data as it changes over time.  E.g RefNo 2 starts on 22/09/17 with amount £1301 and finshed at 15/08/18 amount £3017.93.  I'd like to see if this is common across all data, i.e we finsh higher than we started. 

I'm not sure how I can achieve this or how best to visualise it and would appreciate any help and/or recommendations.

RefNo	Date	Amount	Position
1	26/09/2017	3315	0
2	22/09/2017	1301	4
2	17/10/2017	4600	2
2	13/11/2017	3017.93	1
2	15/08/2018	3017.93	0
3	27/09/2017	1301	1
3	11/10/2017	0	0
4	27/09/2017	0	0
5	09/10/2017	1301	2
5	31/01/2018	1200	1
5	05/02/2018	1162.23	0
6	16/10/2017	1301	3
6	10/05/2018	500	2
6	08/06/2018	414.76	1
6	07/08/2018	414.76	0

[amitchandak]

@MrToast20 , is your issue resolved?

[v-diye-msft]

Hi @MrToast20 

Please kindly check whether you'd like to get below visualization:

Not quite sure about the meaning of position, so I didn't put it in. Please disclose more details about your requirements that I provide further help.

[MrToast20]

Hi @v-diye-msft 

Thanks.  That looks better.  What is your formula for Amount?

Also what does it look like if you select RefNo 2 in the slicer?

[v-diye-msft · Accepted solution]

HI @MrToast20 

I didn't do modelling on the data, just put them in the visual. pbix attached for your ease.

[amitchandak]

Create a new column

Last date = maxx(filter(table,table[RefNo]= earlier(table[RefNo])),table[date])
last value = maxx(filter(table,table[RefNo]= earlier(table[RefNo]) && table[date]= earlier(table[Last date]) ),table[Amount])

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Datanaut Connect on Linkedin