- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Build an if ISNull Calculation between two tables
Hello,
Looking for some help in figuring out calcualtion in my report. I am building a history change report where I download a snapshot of our oppportunity data from Salesforce every Monday morning so the table which is called "SF Opportunity History" contains a coulmn called Report Date which would be the date it was downlaoded. And then I have the Live Opportunity Table Linked from Salesforce in PBI to get the Current Date as of Toady to then calcualte what has changed. Below is a summary of an example for one region where I have done all my calculations and the one I am stuck on is the one I have left blank which should equal the toatl that would be in the Variance Check.
All of the calcualtions are based on records in the SF Opportunity History Table. But for the Opportunities Added to the Region I would have to do the calcualtion in the Opportunity table to pull new opportunity records that have been added since the Report Date. And just note that I can't do it based on the SF Opportunity History latest report date because from the time I download the table to the time I hit Refesh on the PBI and it finshes there can be a change to an opportunity.
So not sure how I would write a measure in the Opportunity table to say sum Contract Value if the Opportunity number does not exist in the SF Opportunity History Table on a Filtered Report Date???? I some one can lead me in the right direction that would be wonderful 😕
Filter SF Opportunity' [Report Date] | 11/18/2019 |
User' [Rep Region] | EMEA |
Prior Week Total Contract Value | $106,008,089 |
Prior Week Lost Total | ($19,725,343) |
Prior Week Booked Total | ($582,900) |
Prior Week Contract Value Change Total | $19,933,735 |
Prior Week Opp Moved Total | ($31,008,691) |
Prior Rep Region Changed Contract Value | ($215,397) |
Opportunities Added to the Region | |
Total Changes | $74,409,492 |
Current Region Pipeline Total CV | $119,174,417 |
Variance Check | $44,764,925 |
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
For the love of $Deity, why ?
There is really no need to do snapshots out of SFDC. SFDC is one of the few systems that actually support proper event based reports.
The "Opportunity Field History" object captures all relevant events in the life of your opportunity. Pull this object and then use helper functions that allow you to calculate the status of any opportunity at any point in time.
You will save massive amounts of space and you will gain down-to-the-second accuracy rather than weekly snaps that are blind to what happened to the opportunity in between them, and that are helpless when business wants to "add just one more field, with history please".
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
That's something I will consider trying in the future when I have spent more time using and I am more advanced in Power Bi and if we ever upgrade to the Enterprise Version of Salesforce where I can just create the history reporting in there. But until that day I need to get my report to work now so that I have something to present to management that they can use now.....
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Provide some sample (sanitized) data for testing, and show the expected outcome.
Small note: Be aware that SFDC is case sensitive. Power Query is a little bit case sensitive, DAX is decidedly not case sensitive. I can show you the implementation of the CaseSafeId() function in Power Query if you like.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Best
D
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I knew I could tease you with that. I am referring to the data part. In SFDC land "0062700000fwfN5" and "0062700000fwfn5" are two very different things.

Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Power BI Monthly Update - February 2025
Check out the February 2025 Power BI update to learn about new features.

Subject | Author | Posted | |
---|---|---|---|
09-02-2024 01:27 AM | |||
10-13-2024 10:35 PM | |||
08-07-2024 12:10 AM | |||
10-09-2024 11:19 AM | |||
09-30-2024 09:21 AM |
User | Count |
---|---|
23 | |
12 | |
10 | |
10 | |
8 |
User | Count |
---|---|
16 | |
15 | |
15 | |
12 | |
10 |