Calculate prior costs per project at the date of revenue

[razmochaev]

Hi everyone,

I'm trying to crack the following problem. I've attached the Excel file with the model and my measures: https://1drv.ms/x/s!Ao_VEs0z3bf5pVpZ360DvfwixfA1?e=ZNIpkV

I have an accounting log that stores sales and costs data for different projects.

Our business logic implies that costs must be written-off only when there is a revenue accounted for a project, and all the costs BEFORE revenue date should be included. If there are several revenue dates, we must write off all the costs between these dates.

I also want to keep lineage to "Expense Item" dimension.

Here's the desired output:

 Here, [Direct Costs] computes all the cost at their posting date. [WrittenOff Costs] is my failing creation. And [Desired Measure] is a workaround to show the desired output.

I'll be very grateful for any help.

[Greg_Deckler]

Maybe something along the lines of :

VAR __Date = MAX([DAte])
RETURN
SUMX(FILTER('Table',[Date]<=__Date),[Cost])

[razmochaev]

@Greg_Deckler , unfortunately I don't have a [Cost] column. [Cost] is a measure.

The formula you suggested (I called it [Values]) is equivalent to [Total Amount] measure in my model: