Track chnages in sales lead status

[ar-data]

I have data from our CRM which has 20+ columns and among many things that need to be visualized on power bi, I need a visual to show:

> 'What was the status of a particular sale lead last week and what is the status today if it has been changed'

I will have a relative date filter where in end user can select filter to see changes in like last 1 week or month/s and the visual should show both old and new status.

1. The data is generated weekly once. Ideally I think we should retain data of all the weeks to track these changes and the status will be frozen once the quarter ends. So I am not sure how the data should be stored - weekly in separate excel sheets etc.

2. If I keep adding weekly excels to the source folder, how can I get the latest unique data to show in other visuals like to track sales agent wise sales achievement, branch/client wise sales achievement?

Kindly help with this and thank you.

[amitchandak]

@ar-data , Check with DAX append method if you can keep old data

https://blog.crossjoin.co.uk/2020/04/13/keep-the-existing-data-in-your-power-bi-dataset-and-add-new-...
https://www.thebiccountant.com/2017/01/11/incremental-load-in-powerbi-using-dax-union/

[ar-data]

I went through the articles and seems that does not help me with my requirement. The incremental refresh may help in easing the data load during refresh, but in my case I should have both old data and new data forever and be able to track changes.

[v-jingzhang]

Hi @ar-data 

It's difficult to tell which would be the best solution for your scenario. Do you have date column in your table which can tell people whether the data is new or old? Maybe you can store weekly data in separate Excel files in a folder, then connect to this folder through Get data > File > Folder and combine all files into a table. In this way, all data is combined in a table and you can load this table into Power BI Desktop to create the report.

In the report canvas, you can add date column into slicers or filters to filter the data to get new and old status. You can also use some specific filters in measures to get the result you want.

References:

https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-combine-binaries

https://radacad.com/power-bi-get-data-from-multiple-files-in-a-folder-on-onedrive-for-business-no-ga...

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.