sum total for max date of each month

[Anonymous]

I have a report that runs weekly for years.  The column "Run Date" has ever unique Saturday date since it began.  

Run Date       Total Cost

02/03/2018      150.00

02/10/2018      200.00

02/17/2018      175.00

02/24/2018      210.00

03/03/2018      125.00

03/10/2018      175.00

I need a DAX formula to return the value for each month after finding the max date of each month. The max date for Feb is 02/24/2018 and the Total cost was 210.00.  The max date for March is 03/10/2018 and the Total Cost was 175.00

Final result should be:

210.00 for Feb

175.00 for March

I tried:

Month End Inv = CALCULATE(sum(Inventory[ExtCost]),LASTDATE(DateDimension[Date]))

but of course it did not work.

[Zubair_Muhammad · Accepted solution]

HI @Anonymous

What if you take LASTDATE from Inventory Table

Month End Inv =
CALCULATE ( SUM ( Inventory[ExtCost] ), LASTDATE ( Inventory[Run Date ] ) )

[Anonymous]

@Zubair_Muhammad 

I know this is a year old but...

Can I ask why the MAX of the Date table didn't work but the LASTDATE from the other table did?  Is it to do with how MAX works with dates or did you suspect the relationships were affecting the MAX function's purpose?

[Anonymous]

I'll tell you what it would do...........it will work perfectly if I do what you suggested. 

Thank you!!