Sum Cost from Multiple Lines into a Single Row

[Anonymous]

Hi everyone, and thanks in advance for any help on this one....it's very much appreciated!!

So to try and summarize, I have a Dynamics 365 Table in Power BI that links to our Projects module and shows Revenue and Costs.  There are 5 Transaction Types in the 'TransactionType' column: "Cost", "Hour", "Item", "Revenue" and "On-Account".  I'm currently using the 3 Cost Type Transactions (Cost, Hour & Item) and the Revenue type, and disregarding 'On-Account'.

Each Project has a unique identifier in Column "ProjectID".  Let's say for project "FO123456", there might be 100 lines.  First 70 lines are cost entries ('TransactionType' of  "Cost", "Hour" and "Item"), while line 71 has a 'TransactionType' of "Revenue", then lines 72-99 are additonal cost lines, and finally line 100 is the final Revenue Recognition line.  All of these transactions happen on different dates over multiple months.  Every single line (Revenue or one of the 3 Cost Types) is shown on an individual line.  If it's a Revenue Line it has zero in the 'TotalCostAmount' column and a figure in the 'TotalSalesAmount' column, while if has a 'TransactionType' of "Cost", "Hour" or "Item", there is a figure in the 'TotalCostAmount' column and a zero in the 'TotalSalesAmount' column.

Screenshot which hopefully visually depicts what I'm talking about shown below.

Using the above example, I currently have a Power BI set-up to show Revenue and Cost by month based on the month that the Revenue line is in.  However I just realized that what the Power BI is doing is showing only the cost transacted in the same month as the Revenue properly in the month that the Revenue transacted.  What I need it to do is show all of the cost prior to the Revenue Date alongside that Revenue.  So in the above example, all of the cost from lines 1-70 in the same period as the Revenue from line 71, then the cost from lines 72 through 99 alongside the Revenue on Line 100.

If what I'm trying to depict here is making any sense to anyone, could someone help with figuring out a formula to do this?  I was thinking a formula that would filter by project ID first, then sum the rows with 'TransactionType' of "Cost", "Hour", "Item" with a transaction date of or earlier than the date on the "Revenue" line (??).

[lbendlin]

Please provide sample data in usable format (not as a picture) and show the expected outcome.

[lbendlin]

create a measure to 

- identify the revenue row

- get its timestamp

- find the last revenue row that occured before this one

- sum up the cost items that happened in the dates between those two timestamps

- return it for the revenue line.  That will tag it to the right month.

[Anonymous]

Thanks so much @lbendlin , very much appreciated.  

Back to the community, any pointers on how I might go about seeting up this measure?  I created quite a few reports in the past, but nothing with really more than quick measures.  I'm not sure I'd know how to go about setting this one up.

[lbendlin]

• This sounds like a rather fragile process. Can you guarantee that there will ever only be a single revenue line in between cost lines? Can you guarantee that the cost timestamp absolutely reliably is before the revenue timestamp? What if there is no cost entry betwen two revenue entries? What if there is no revenue for a group of cost records?

[Anonymous]

I think guarantee is a strong word, and I can't guarantee these situations would not occur.  That being said, I think that presumably and reliably, 

1. There would only be a single revenue line in between cost lines
2. cost timestamps would be before revenue timestamps
3. a revenue recogniation process would not be run or in the case of multipl rev rec 'Estimate' processes re-run unless there was additional cost
4. I would expect there to be solid 6-9 month timespans where cost entries are happening but for which the first revenue recognition process would not yet occur