Forum Discussion

shanipowerbi's avatar
shanipowerbi
Helper 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 

     

     
     
mhossain's avatar
mhossain
Solution Sage
5 years ago

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's avatar
    shanipowerbi
    Helper 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.

     

    • mhossain's avatar
      mhossain
      Solution Sage
      5 years ago

      shanipowerbi 

       

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

       

       

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

      shanipowerbi 

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