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!
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))))
You could also download the pbix file to have a view.
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?