Build an if ISNull Calculation between two tables

[janmack79]

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

[lbendlin]

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".

[janmack79]

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.....

[lbendlin]

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.

[Anonymous]

As much as Power Query's M language is concerned, it's totally case sensitive, not just "a bit."

Best
D

[lbendlin]

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.