Week-on-week Analysis

[Anonymous]

Afternoon, 

I have a quick query regarding PBI's ability to track week-by-week changes in data. 

Currently, I am pulling Salesforce information into Excel due to the 2000-row limit on SF API. I am trying to track changes in prospect value - basically, the process is that the Salesperson keeps the "Estimated Sales Value" field up to date depending on information from client. This varies week-by-week and month-by-month. 

At the minute, the only way I can monitor fluctuations is to take SF pulls and create new tabs in Excel, then compare the ESV field between dates. 

Is there a way in PowerBI to save previous versions of a Data Source for reference? Ideally,I want to be able to work everything from 1 Excel file, but if I do this, it overwrites previous versions of the data. 

I have attached an image of what I would ideally want to achieve. The table "Week 2 Comparison" is the goal. 

Hope this makes sense?

Kind Regards, 

Martin

[lbendlin]

Power BI has no memory, and cannot store history in any reliable form.

Also, you're holding the problem wrong.  When working with SFDC data you should not use snapshots, but rather use the existing object field history reports that record all object change events.  That gives you MUCH better accuracy (in your example, the Estimated Sales Value can change many times during the week but you would not know if you only used snapshots), with MUCH lower storage requirements.

Chek out the Report Types with "History"  in their name.

[Anonymous]

@lbendlin Thank you. I have tried using SF reports but the 2000-row limit makes this unusable. I haven't yet played around with impoting SF Objects. To be honest, my SF knowledge is pretty basic and this seemed to require a fairly detailed knowledge of how SF works. 

Any good resources you know of to get up to speed with this?

[lbendlin · Accepted solution]

For me the Metadata API was the best resource to learn how SFDC actually works and how to take advantage of that design (which is pretty neat in my opinion).

Warning: SFDC object IDs are case sensitive. Power BI is not. Use caution, or use the CaseSafeID values.

For my reporting needs I ended up relying nearly exclusively on CSV extracts. Now row limits, and good ingestion performance. YMMV.

[Anonymous]

@lbendlin  Awesome, thanks. I think sorting the Object History Tracking and pulling CSVs seems the most straightforward way to do it. 

[mahoneypat]

Typically with such APIs you can use pagination to make multiple API calls to get all the data.  Your query can be set up to take advantage of that.  Does the API have the ability to provide a skip or skiptoken in the web call?  If so, this can be done.

Regards,

Pat