Rolling Sum at Snapshot in Time While Checking for Most Up to Date Records

[arhomberg]

Hello everyone,

This is my last chance to try and get this issue figured out. I am new to Power BI but have been trying everything in the book to work and solve this problem, but with no luck. Basically, I am trying to find a measure of our company’s open sales opportunities at various snapshots in time.

I have a table which houses all historical records of every stage of an opportunity. This includes an entry when the opportunity is first created, when it is updated, and when it is closed (won or lost). Here is an example of what the lifetime of a single opportunity would look like.

We have thousands of these opportunities sorted by the time they were created in our table. This problem stems from two others that I have already worked on recently within the Power BI community.

Major help from @daxer-almighty 

Solved: Find a rolling sum for a specific moment in time - Microsoft Power BI Community

Major help from @AlexisOlson 

Solved: Re: Find a rolling sum for a specific moment in ti... - Microsoft Power BI Community

This is the code that I am currently on, but I cannot seem to get the commented-out portion to work. Whenever I run it with the portion included, no calculation is returned.

Result when code is included:

Result when code is not included:

Code (with portion commented out):

‘Salesforce_opportunityhistory’ is the table that houses all the historical opportunity records.

[Is Pipeline] is a column I created in the table that checks to see if the record is a record from an open opportunity, or if it is a record that shows a closing opportunity (If an opportunity is closed, it gets its own record inputted to show that it was closed). We only want to look at the opportunities that we have/had open at certain instances in time.

I have a dates table that looks at all the possible dates and am trying to filter through to find the sum at each specific date in time.

The main issue with all of this is that an open record can have multiple entries in the table as a result of updates. Whenever the opportunity is updated, a new record is posted with the updated information and system modification time stamp [systemmodstamp].

So what I have been doing is trying to find all the open opportunities at a given time by checking to make sure the [createdate] is less than the [LastVisibleDate], and the Last Visible date is less than the [Close date]. You will notice in the code that I have [Recent close] as a column substituted for [closedate].

I did this because the close date is a field manually entered into the system by an employee, so a close date could be put in for tomorrow, the time could pass and the opportunity could still be open as nothing has been done to it, then it could be modified to have a later close date; so the system mod stamp is a better showing of the lifetime of that specific record.

What I am trying to do, ultimately, with the commented out section of code is to check the system mod stamp to sum only if it is the most recent record. Duplicate records could qualify for summing because they might have a create and close date in the same range, but have been edited multiple times, so only one is technically the most recent record. The result I show above resulting from when the code is commented out is what I belive to be the summ of all the amounts in those period of time, including the 'duplicate' entries. I am now just trying to get it to simply look at the most recent record for that given [opportunityid]

Any advice on how to approach this would be amazing, thank you!

[selimovd]

Hey @arhomberg ,

in general I think I understand more or less what you want to do, but I still don't really understand where you're struggling.

Can you maybe summarize what you want to do and where you struggling in a few sentences?

If you want help from other people, make it as easy as possible for them. So maybe put all the data from your screenshots in a Power BI file with which I can work. Otherwise I have to start typing the data from your screenshot what is not fun at all as you might imagine and create a data model that might fit with yours, but maybe it doesn't and then all my work was useless.

Best regards

Denis

[arhomberg]

Hi @selimovd 

To summarize, I am simply trying to get the chunk of code that is commented out in the screenshot to work. 

What the code is looking to do is to filter to the most recent system mod stamp for each opportunity id. 

I think that this is the bottom line what I would like to solve. If the issue is too complex for people I would be happy to upload some documents, but I feel that getting the small code chunk to run is my primary problem. Thanks!

[PaulOlding]

As selimovd said it's hard to help without any data to work with.  I suspect the problem is using ALLEXCEPT as a table function rather than a calculate modifier.

Try removing the FILTER function

[arhomberg]

@PaulOlding @daxer-almighty @selimovd 

Here is the link to the project: https://www.dropbox.com/s/mfi56pbdxbiklf5/Power%20Bi%20Problem.pbix?dl=0

Sorry all for the confusing and lengthy problem. You guys are awesome to providing your insights! To simplify (hopefully) the problem at hand, I am trying to take these historical records and find the sum of the amount of opportunities that were open over time. 

Ex. we could look at January and see the sum of the amount of opportunities that were open at that time.

I hope this helps, and again, thanks so much for the assistance. Any guidance on this is ubeleivably appreciated!

[daxer-almighty]

Well, can't you just create a table that will hold the relevant records only and get rid of all the fluff? You can do that in Power Query with ease. If your DAX becomes complex, it's high time to refactor your model. Easy as that.