cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
razmochaev
Helper I
Helper I

Calculate prior costs per project at the date of revenue

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:

DesiredOutput.png 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.

2 REPLIES 2
Greg_Deckler
Super User
Super User

Maybe something along the lines of :

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

@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

@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:

Gregs Formula.png

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors