Year To Date based on Maximum Date in Column

[rijnhardtk]

Hi 

I need to calculate the Year To Date to the maximum value of a column. 

Ie. If the latest date in the dataset is 2 March, I need it to calculate YTD to 2 March, not 23 March (today's date)

Here is my attempt at a query. 

Pt Basket YTD = 
CALCULATE(
    CALCULATE( // total revenue
        AVERAGE('Calendar'[Total Revenue]), 
        FILTER('Calendar', 'Calendar'[Total Revenue] > 0)
    )
    / //div
    CALCULATE( // platinum production
        AVERAGE('Calendar'[Platinum - Production]), 
        FILTER('Calendar', 'Calendar'[Platinum - Production] > 0)
    ), // start of filter
    YEAR('Calendar'[Date]) // date of data
    =
    CALCULATE( // find the maximum date in data
        MAX('Calendar'[Date].[Year]),
        ALL('Calendar')
    )
)

Assistance is appreciated!

BONUS QUESTION

How do I translate this to a month to date calculation? 

[Phil_Seamark]

HI @rijnhardtk

That doesn't look like a Year to Date calculation (Cumulative over a year)

I recommend having a look at the TOTALYTD function and you could build your measure along these lines

Pt Basket YTD = 
VAR ReturnVal = 
    TOTALYTD(
        DIVIDE(
            AVERAGE('Calendar'[Total Revenue]) , 
            AVERAGE('Calendar'[Platinum - Production])
            ) , 
            'Calendar'[Date]
            )
VAR MaxDateInTable = MAX('Calendar'[Date])

RETURN 
    IF(
        MAX('Calendar'[Date])<=MaxDateInTable,
        ReturnVal
        )

Then if you use this measure in a visual with the 'Calendar'[Date] field on your Axis, you might be close.