Forum Discussion
Last Invoice Payment Status
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.
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.
6 Replies
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
FrankAT its Not working, I need Latest status in 2nd table
shanipowerbi , new column in table two
maxx(filter(Table1, Table1[User ID] =Table2[User ID]),lastnonblankvalue(Table1[Date],max(Table1[Payment Status])))
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
