Compare two Sharepoint lists and displaying the variance in PowerApps

[HRY]

Hello @abm  I am trying to create an app which is connected to two SharePoint lists with same column names and column types. One is forecasted expense and the other is actual expense. I wanted the user to be able to compare the two SharePoint lists on a click of a button and the variance to be displayed in PowerApps. Eg. resource A was supposed to claim 500 USD as per the forecast for the month of Jan 2021 but in actuality he claimed 200 USD in Jan 2021 then the table in power app should show the details: resource name, month, forecasted and actuals and variance.
The SharePoint lists contains columns: Resource Name (people picker), Expense Amount (Number), Expense Description (Single text), Expense type (choices), Billable/Non billable(choices), Month(Choices), Year(Choices).

Also, there can be more than one entries for a resource with difference expense types, expense description and expense amount for a month. The comparison should be done according to the total expense amount claimed by the resource for that particular month (irrespective of the expense type/expense description).

[abm]

Hi @HRY 

Thanks for posting the question. So from Power Apps you going to pass the selected list for comparison? Or do you want to compare all list records in both? Please let me know.

Thanks

[abm]

Hi @HRY 

Also please have a look at this video tutorial

https://www.youtube.com/watch?v=Lxuv_vEvFbM

[abm]

Hi @HRY 

Ignore my earlier comment. Please watch this tutorial.

https://www.youtube.com/watch?v=Pl3mzNOqDxw&t=9s

[HRY]

Hello @abm , Yes, the user would select the month and the year in PowerApps screen and click on show variance button which will then display the list of resource names, forecast expense, actual expense and variance for that particular month and year.

[abm]

Hi @HRY 

Watch the YouTube video tutorial  which I mentioned earlier. Again you can extend the conditions to include forecast expense type as well. Once you filtered you will have the actual expense and variance to work out. Remember you need to two Get Items. One for the first and other for second.

Thanks

[abm]

Hi @HRY 

If you still need further help I am happy to do a screen share. Let me know.

Thanks

[HRY]

Hello @abm Do I need to create a 3rd Sharepoint list - a combination of the two sharepoint list using filter Query in get items and then display the 3rd list in PowerApps? Yes, we can have a screen share.

[abm]

Hi @HRY 

How you progressing with this? Please let me know.

Thanks