Forum Discussion
Aggregating calculation by months within a variable
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
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
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!