Showing results for 
Search instead for 
Did you mean: 
supersten Frequent Visitor
Frequent Visitor

Transactional history data to snapshot format?

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!


Community Support Team
Community Support Team

Re: Transactional history data to snapshot format?

Hi @supersten,

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))),



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



Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
supersten Frequent Visitor
Frequent Visitor

Re: Transactional history data to snapshot format?

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?