Last Invoice Payment Status

[shanipowerbi]

Hi Experts I need your help.

I have two tables in which 1st table named "Invoice Data" containing columns (User IDs not Unique)

Date / User ID/ Amount / Invoice ID / Payment Status (Paid or Unpaid)

My 2nd table "User Data contains columns (User IDs Unique)

User ID / User Conversion Date / Channel

I have made a relationship b/w User IDs, actually, I need the Last Payment status (Paid or Unpaid) in my 2nd table.

[amitchandak]

@shanipowerbi , new column in table two

maxx(filter(Table1, Table1[User ID] =Table2[User ID]),lastnonblankvalue(Table1[Date],max(Table1[Payment Status])))

[shanipowerbi]

@amitchandak Formula not working.

[shanipowerbi]

@amitchandak @FrankAT 

This is my table 1

and my second table is one have User IDs in result answer should be "paid" if look July 31 2020

[v-zhenbw-msft · Accepted solution]

Hi @shanipowerbi ,

We can use a measure or a calculate column to meet your requirement.

1. Create a measure like this,

Last Payment Measure = 
var _maxdate = CALCULATE(MAX('Table 1'[Date]),FILTER(ALLSELECTED('Table 1'),'Table 1'[User ID]=MAX('Table 1'[User ID])))
return
CALCULATE(MAX('Table 1'[Payment Status]),FILTER('Table 1','Table 1'[Date]=_maxdate))

2. Create a calculate column in Table 2 like this,

Column = 
var _maxdate = CALCULATE(MAX('Table 1'[Date]),FILTER('Table 1','Table 1'[User ID]='Table 2'[User ID]))
return
CALCULATE(MAX('Table 1'[Payment Status]),FILTER('Table 1','Table 1'[Date]=_maxdate&&'Table 1'[User ID]='Table 2'[User ID]))

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

BTW, pbix as attached.

[FrankAT]

Hi @shanipowerbi 

use a calculated columnin the second table with the following formula:

Payment Status (Paid or Unpaid) =RELATED('Invoice Data'[Payment Status (Paid or Unpaid)]

Regards FrankAT

[shanipowerbi]

@FrankAT its Not working, I need Latest status in 2nd table