Transactional history data to snapshot format?

[Anonymous]

Long time lurker who for the first time cannot find a good answer to a question in this forum!

Whats the simplest, and lowest overhead, way of transforming transactional history data to a snapshot format?

We're using Salesforce CRM, which we then query into PowerBI to build our sales dashboards, so here's the simple example: An opportunity in the sales pipeline can change status, with a modified date, in the history table. How would I get from this history table:

Opportunity   NewStage    OldStage     Time

Oppty-1         Won           Upper Pipe   2018-11-26

Oppty-1         Upper Pipe  Mid Pipe      2018-11-20

Oppty-1         Mid Pipe      Lower Pipe  2018-11-09

To this form of snapshot table:

Opportuntiy   Current        7-days ago        14-days ago    

Oppty-1        Won             Upper Pipe         Mid Pipe

I'm guessing there's an easy answer for this out there somewhere!

[v-danhe-msft]

Hi @Anonymous,

Based on my test, you could use the below formula:

Create a new table:

Table = SUMMARIZE('Table1','Table1'[opportunity],"Current",CALCULATE(MAX('Table1'[NewStage]),FILTER(ALL('Table1'),'Table1'[Time]=DATE(2018,11,26))),
                                                 "7-days-ago",CALCULATE(MAX('Table1'[NewStage]),FILTER(ALL('Table1'),'Table1'[Time]=DATE(2018,11,20))),
                                                 "14-days-ago",CALCULATE(MAX('Table1'[NewStage]),FILTER(ALL('Table1'),'Table1'[Time]=DATE(2018,11,09))))

Result:

You could also download the pbix file to have a view.

Regards,

Daniel He

[Anonymous]

Nice, thats almost what I was looking for. I assume that the MAX function won't work well if I have several movements within the same date span (i.e. between two snapshot dates), since I would want the last change before the "snapshot" date. Is there an alternative?

Thanks!