Forum Discussion

shanipowerbi's avatar
shanipowerbi
Icon for Helper III rankHelper III
5 years ago
Solved

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...
Need Help
  • vpanchu's avatar
    vpanchu
    5 years ago

    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's avatar
shanipowerbi
Icon for Helper III rankHelper III
5 years ago

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's avatar
vpanchu
Icon for Helper I rankHelper I
5 years ago

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's avatar
    shanipowerbi
    Icon for Helper III rankHelper III
    5 years ago

    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's avatar
      vpanchu
      Icon for Helper I rankHelper I
      5 years ago

      @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 🙂