Forum Discussion
Need Point in Time Last Invoice Amount
Hi Experts
I need your urgent help, I have two tables 1 - Active Users (Servers) 2 - Invoice Data
| Active Users (Servers) | |
| Date | User ID |
| Feb-20 | 126389 |
| Feb-20 | 126611 |
| Mar-20 | 126389 |
| Mar-20 | 126611 |
| Apr-20 | 126389 |
| Apr-20 | 126611 |
| May-20 | 126389 |
| May-20 | 126611 |
| Jun-20 | 126389 |
| Jun-20 | 126611 |
| Jul-20 | 126389 |
| Jul-20 | 126611 |
| Aug-20 | 126389 |
| Aug-20 | 126611 |
| Sep-20 | 126389 |
| Sep-20 | 126611 |
| Invoice Data | ||
| Date | User ID | Invoice Amount |
| Jan-20 | 126389 | $ 10 |
| Feb-20 | 126389 | $ 15 |
| Feb-20 | 126611 | $ 10 |
| Mar-20 | 126389 | $ 20 |
| Mar-20 | 126611 | $ 20 |
| Apr-20 | 126389 | $ 25 |
| Apr-20 | 126611 | $ 30 |
| May-20 | 126389 | $ 30 |
| May-20 | 126611 | $ 40 |
| Jun-20 | 126389 | $ 35 |
| Jun-20 | 126611 | $ 50 |
| Jul-20 | 126389 | $ 40 |
| Jul-20 | 126611 | $ 60 |
| Aug-20 | 126389 | $ 45 |
| Aug-20 | 126611 | $ 70 |
| Sep-20 | 126389 | $ 50 |
| Sep-20 | 126611 | $ 80 |
Both tables are separate the result I need the amount in a
| Event Date | ||
| Date | User ID | Point of Time Invoice Last Invoice |
| Feb-20 | 126389 | $10.00 |
| Feb-20 | 126611 | $0 |
| Mar-20 | 126389 | $15.00 |
| Mar-20 | 126611 | $10.00 |
| Apr-20 | 126389 | $ 20.00 |
| Apr-20 | 126611 | $20.00 |
| May-20 | 126389 | $25.00 |
| May-20 | 126611 | $30.00 |
| Jun-20 | 126389 | $30.00 |
| Jun-20 | 126611 | $40.00 |
| Jul-20 | 126389 | $35.00 |
| Jul-20 | 126611 | $50.00 |
| Aug-20 | 126389 | $40.00 |
| Aug-20 | 126611 | $60.00 |
| Sep-20 | 126389 | $45.00 |
| Sep-20 | 126611 | $70.00 |
Step 1---Create a bridge table with unique user id
Step 2 --Create a calculated colum in the Active user table
Pending Invoice last month =LOOKUPVALUE('Invoice data'[Invoice Amount],'Invoice data'[Date],DATEADD('Active User'[Date],-1,MONTH))Step3----pull the highlighted fields in a tableNote: it is not showing the Feb value for user 126389 because there is no data in the active user table , you can work on the source , but hope you got the context.
Rergards
Vpanchu
9 Replies
Create a calculated column in your "Active user" table as below:
Point of Time Invoice Last Invoice=
LOOKUPVALUE(Invoice_Data[Invoice Amount],Invoice_Data[Date],DATEADD(Active_Users[Date],-1,MONTH))
Make sure you have created relationship between the tables on the userID.
Please let me know if this solves.
Thanks for the help, how can I make the relationship as you can see both tables have multiple User IDs of the same User.
