Forum Discussion

rijnhardtk's avatar
rijnhardtk
New Member
8 years ago

Year To Date based on Maximum Date in Column

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...
Need Help
Phil_Seamark's avatar
Phil_Seamark
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

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.