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)

 

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? 

Need Help

1 Reply

  • 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.