Measure to calculate weighted average of days a stock was held before being sold

[leolapa_br]

Below is a snippet of a facts table 'fTrans' containing historical purchase and sale transactions of shares of stock.

• [Ticker], [Date], [Sale date related to respective purchase], [Transaction], [Shares] and [Price] are the imported data.
• [Days] is a measure where [Sale date related to respective purchase] - [Date], hence the time each batch of shares was held before being sold.
• And [Purchase total] is another measure that computes [Shares] x [Price].

What I need is to calculate the AVERAGE of days each batch of shares was held until its respective SALE transaction took place.

As you can see by the expanded snippet below, this is quite a straightforward exercise when a particular sale transaction relates one-on-one to a prior purchase transaction.

However things get a bit more complicated as there are instances where a particular sale refers to TWO OR MORE prior purchase transactions, which I highlight some examples below.

And to complicate matters further I DO NOT want to calculate a simple straight average by just summing up the days and dividing them up by the number of occurrences.

That average calculation needs to be a weighted average using [Purchase total] as the weight basis for each transaction.

So I started constructing a couple of temporary tables and then use the second one as the source table to grab the calculated average days for each respective sale date from by writing a third and final RETURN block of the measure code.

On the intermediary table 'Supp_Tbl' I calculate the product between [Days] and [Purchase total], which will be used as the weight for each purchase transaction.

Then I use 'Supp_Tbl' to calculate the [Weighted average days] and setup the second table 'Days_Tbl' by grouping the [Product of Days and Purchase total] and the [Purchase total] by [Ticker] and [Sale date related to respective purchase] so I can divide them up and then get the result for each sale date.

And that's where I get stuck. I do have the results right in front of me and know what needs to be done on the RETURN block in order to get to the measure:

1. Call 'fTrans' table and get each [Ticker]/[Date] combination.
2. Call 'Days_Tbl' and scan it to find each row whose [Ticker]/[Sale date related to respective purchase] combination matches the one from step 1.
3. Grab the respective [WghtdAvgDays] from the corresponding row found on step 2.

I just seem not to know how! I've tried several paths such as setting up a third temporary table, INDEX or LOOKUPVALUE but to no avail. Maybe my mind is still too stuck in Excel and prevent me from thinking the DAX way.

Any help is immensely appreciated...

[some_bih]

Hi @leolapa_br in Row 13 in your measure you include filter criteria, to return BLANK(), so calculation is "good".

Did I answer correctly? Kudos appreciate / accept solution

[leolapa_br]

@some_bih both codes I shared on those two screenshots are OK, as I checked the results highlighted on the second screenshot and they are fine.

The problem is not with the code I've developed so far, since they get me 90% of the way to accomplish what I need.

My problem is getting the final 10% of the code that will get me there. I just don't know how to grab the results I calculated and get them to the rows they relate to on the fact table.

Maybe I didn't express myself right in the way I originally phrased my question and I apologize for that. I re-wrote the very end of the question to make my question/problem more clear.