Building a date-centric measure for sparse data snapshots

[creeves]

We have customized Dynamics to track when key Sales Opportunity values (like estimated revenue) change. The goal is to look back at a series of Sales Opportunities to see how their value, independently and in total, changed over time.

Each change is recorded in a timestamped snapshot record associated with the parent Opportunity. The result, after some manipulation, is a table summarized in the image, with each row representing the changed values and the date they occurred. To keep the data as concise as possible we record each Opportunity change only when it occurs, and retain only the most recent change in each month. If the Estimated Revenue for an Opportunity does not change in a particular month we will assume that the value in that month was unchanged from the most recent (prior) snapshot. There is always at least one snapshot: from the date the Opportunity was created. 

The challenge that has defeated me so far is to create a measure that, for each calendar month, will calculate the estimated revenue for an opportunity (or sum for a set of opportunities). For example, if the most recent snapshot for an opportunity was in June, then subsequent months would use the June value until wit was updated in, say, October.

The attached image is a simplified sample of the data structure and the desired results. I'd appreciate any help!

[v-shex-msft · Accepted solution]

HI @creeves,

It seems like a common date range define by multiple date fields analysis requirement. I'd like to suggest you take a look at the following blog start date, end date part if helps:

Before You Post, Read This: start, end date  
Regards,

Xiaoxin Sheng

[creeves]

@v-shev-msft

Thank you again for the reference material. I went through each example and believe that I now understand the underlying principle: a virtual table which, for each entity (Customer or Ticket #) holds the key data in question (the customer obligation each month, or the dates the ticket is open). The measure is calculated as the the sum of the value of open accounts in a period, or the count of open tickets in a period.

In my case, the key is similar: to envisage a table that shows the value of, say, estimated revenue, in each period for each opportunity. For example, the estimated revenue for product A in May 2022 is $2,000; for B it is $3,000; total $5,000.

The challenge I still face is how, for months for which I have no snapshot of estimated revenue, to use the most recent prior value. I've expanded the example to clarify the problem .. which is how to infer the intermediate/missing monthly snapshots. The actual snapshots are in black and those to be inferred are in red. I'd welcome any additional advice.

[creeves]

First, thank you for the prompt reply and please accept my apology for not replying sooner. I was traveling  on a family matter and, unexpectedly, had no time to keep working on this. I have now restarted work on the problem, and the "open ticket" problem appears to address my questions. It will take me some time to understand the example in detail and apply it. I will reply again as soon as I have made some progress or have more detailed questions.