Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
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
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |