Calculate YTD with most recent dates

[bswank31]

Hi, I am struggling with getting my YTD amounts for my most recent dates to calculate correctly. Below is what I have. I hope this makes sense. I greatly appreciate any help I can get. 

YTD = CALCULATE(

    SUM('Expenses'[Expenditure]),

    'Expenses'[Category] IN {"YTD"}

This returns the correct amounts when I select a single month, but when I select numerous months it is adding all the YTD amounts. I only want it to give me the amount for the most recent dates.

Here is what I get when I select each month which is correct:

January

     

February

March

Here is Jan, Feb and March which is a total of all amounts for YTD and not what I want. It should be only March YTD totals.

Here is the data I am using. What I want is when I select all my months, I want the YTD to only show the amount for the most recent dates. In this case, the YTD amounts for march.

I also created a calculated field

YTD Total = SUMX(FILTER('Expenses', ('Expenses'[Date])= MAX('Expenses'[Date])), [YTD])

I thought this would work since it is filtering by the Category YTD but when I select just one month it is sometimes combining the YTD and Expense category.  I am confused on why it is doing that when I just have YTD filtered.

I hope this makes sense and someone can help me out. Greatly appreciate any help I can get.

THANKS!

[bswank31]

thank you all for your responses. After reading what you have suggestest, I have figured out this issue. 

Final YTD = CALCULATE(

    SUM('Expenses'[Expenditure]),

    'Expenses'[Category] IN {"YTD"}, 'Expenses', ('Expenses'[Date])= MAX('Expenses'[Date]))

[Ashish_Mathur]

Hi,

Create a Calendar Table with calculated column formulas for Year, Month name and Month number.  Sort the Month name by the Month number.  Create a relationship (Many to One and Single) from the Date column of the Expenses table to the Date column of the Calendar Table.  Ceate 2 slicer - one for Year and another for Month (Ensure that both these fields are dragged from the Calendar Table).  Write this measure

YTD sales = calculate(SUM('Expenses'[Expenditure]),datesytd(calendar[Date],"31/12"))

Hope this helps.

[lbendlin]

In order for this to work you cannot neglect the year.  

Find the maximum YearMonth that has data, and then present the sum of values for that YearMonth.