Need Point in Time Last Invoice Amount

[shanipowerbi]

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

[mhossain]

@shanipowerbi 

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.

[shanipowerbi]

Thanks for the help, how can I make the relationship as you can see both tables have multiple User IDs of the same User.

[vpanchu]

@shanipowerbi 

It should work with many to many relationship as well , as @mhossain  said.

[vpanchu · Accepted solution]

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 table

 

 

 Note: 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 

[shanipowerbi]

Hi @vpanchu 

Thanks It's working but the problem I am facing is it's taking too much time for calculation. I have data from 2018 and each day I get around 24K rows in Active data and increasing with business. Is that any optimize way to get this data?

Regards

Shan

[vpanchu]

@shanipowerbi

1- you can try incremental load if you have pro account. (i am not sure What is your subscription)

2- you can Try giving one to many relationship insted of many to many.

3- Try to avoide columns with high cardinality if you dont really require it.

4- Use measures wherever required instead of calculated column.

Regards

Vpanchu

Mark the answer if its working for you, it may help other.

Please give Kudos if it was informative in any way 🙂

[shanipowerbi]

Hi @vpanchu

We are using Pro Account

[vpanchu]

Hi @shanipowerbi

Have you tried incremental Refresh , instead of loading all the data every time?

because 24 k rows can be handled by PowerBI very easily its nothing.

https://docs.microsoft.com/en-us/power-bi/admin/service-premium-incremental-refresh#:~:text=Incremen....

Regards

Vinayak

[mhossain]

@shanipowerbi 

You can create many to many relationship, do you see any challenges?