cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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!

 

2 REPLIES 2
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))),
                                                 "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:

1.PNG

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

 

Regards,

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?

 

Thanks!