Aggregating calculation by months within a variable

[GuillaumeB]

I have a table that has a structure similar to this:

Item	Date	Profit	Cost1	Cost2
A	1-jan	9.53	0.15	7.10
A	31-jan	9.50	0.15	7.10
A	01-Feb	9.48	0.20	7.15
A	28-Feb	9.75	0.20	7.15
A	01-Mar	9.68	0.25	7.20
A	31-Mar	9.40	0.25	7.20
B	1-jan	5.90	0.21	4.20
B	31-jan	5.78	0.21	4.20
B	01-Feb	5.89	0.26	4.25
B	28-Feb	5.87	0.26	4.25
B	01-Mar	5.67	0.28	4.30
B	31-Mar	5.33	0.28	4.30

I need to aggregate, inside a measure (not a physical table), considering ONLY the last day of each month through the following equation: (Profit-Cost1-cost2)/Cost2, combining all items together for each end of the month. 

I basically ignore all rows that aren't on the last of the month and calculate the result (which is  %) for each month so I can aggregate using Product/ProductX.

Ideally it would result in something like this:

Date	Equation	Answer
31-Jan	((9.50+5.60)-(0.15+0.21)-(7.10+4.20)/ (7.10+4.20)	0.320354
28-Feb	((9.75+5.89)-(0.20+0.26)-(7.15+4.25))/ (7.15+4.25)	0.329825
31-Mar	((9.75+5.89)-(0.20+0.26)-(7.15+4.25))/ (7.15+4.25)	0.174419

And then the rest of my measure will be to Product() the Answers column.

The fact that I need to Product and still slice based on other columns means I can't use a physical table, I have to use a measure and I've had no luck with the Summarize function. 

Would anyone know how to go about doing this?

Let me know if you have questions, I tried to be as precise as possible.

[lbendlin · Accepted solution]

Your sample data does not agree with your explanation of the expected outcome. For example there is no 5.60 value (did you mean 5.78?) and no 5.89 value (did you mean 5.87 and 5.33 ?

First step: Create a calculated column identifying the month:

YearMonth = FORMAT('Table'[Date],"YYYYmm")

Next:  Write the measure :

Measure = 
// find max date per month
var a = GROUPBY('Table','Table'[YearMonth],"d",maxx(CURRENTGROUP(),'Table'[Date]))
// calculate quotient
var b = ADDCOLUMNS(a,"q",CALCULATE(DIVIDE(SUM('Table'[Profit])-SUM('Table'[Cost1])-SUM('Table'[Cost2]),SUM('Table'[Cost2]),0),Filter('Table','Table'[Date]=[d])))
//multiply result
return PRODUCTX(b,[q])

And finally, put the yearmonth and the measure into a table.

The way the measure is written will work both for the column total and the individual rows

[GuillaumeB]

Absolutely for the sample data, I wrote it on the fly and I think I messed up the math.

That being said, your solution absolutely worked and basically boiled down my +1,000 line Dax measure to under 25 lines. That's a lifesaver. Thanks mate!