How to FILTER a previous year's entry in a Calculated Column?

Question

Hi all,I have a dimension table that contains entries for each company that manages fund assets. I have created a measure on the table, called AUM (Assets Under Management) that aggregates the total value of Funds from the fact table at the reporting date which works well (side question: is that appropriate to have this measure on the dim table?).&nbsp;Dim CompanyIDKeyReport DateNameAUMYoY Change1232016123112331-12-2016Big Co100001232017123112331-12-2017Big Co12002001232018123112331-12-2018Big Co14502504562016123145631-12-2016Small Co10004562017123145631-12-2017Small Co110104562018123145631-12-2018Small Co1155I would now like to create a calculated column called YoY Change to show the Year-on-Year change of the AUM&nbsp; for each company/report date but I'm struggling with the filtering to retrieve last year's AUM for that ID - I think I've fallen foul of the evaluation context but can't work it out.&nbsp;&nbsp;&nbsp;YoY Change = 
    VAR ThisYearTNAV =
        CALCULATE (
            [AUM],
            FILTER (
                dimCompany,
                MAX(dimCompany[Report Date]) 
            )
        )    
    VAR LastYearTNAV =
        CALCULATE (
            [AUM],
            FILTER (
                dimCompany,
                DATE (YEAR(dimCompany[Report Date])-1, MONTH(dimCompany[Report Date]), DAY(dimCompany[Report Date])) 
            )
        )        
    RETURN ThisYearTNAV - LastYearTNAV&nbsp;Also, I'm not sure how to deal with the YoY change at the start of the data series but that's the least of my worries at the moment.Many thanks in advance,Andy

bexbissell · Accepted Answer

Strangely I fell foul of the spam detector so this has just gone live!During the radio silence I revisited the code and I have made some changes and found a working solution. This is now a calculated column so only affected on data refresh (bi-annual) so although it's probably not the most efficient or elegent solution it does work (happy to receive critique):&nbsp;YoY Change = 
// Calculate last year's Report Date
VAR LastYear =
    DATE ( dimCompany[Report Date].[Year] - 1, dimCompany[Report Date].[MonthNo], dimCompany[Report Date].[Day] ) 
    
// Lookup last year's TNAV using 'last year' and the Service Provider's ID
VAR LastYearTNAV =
    LOOKUPVALUE (
        dimCompany[$ Assets in US],
        dimCompany[Report Date], LastYear,
        dimCompany[ID], dimCompany[ID]
    )
RETURN
    // If start of data series just return zero
    IF ( LastYearTNAV = 0, 0, dimCompany[$ Assets in US] - LastYearTNAV )&nbsp;&nbsp;

Forum Discussion

How to FILTER a previous year's entry in a Calculated Column?

2 Replies

Related content

TMDL - calculated column

Facing challenge to calculate previous year QTD

Previous Month's MTD calculation

previous month calculation

Slow visual calculation (PREVIOUS and RUNNINGSUM)

ID	Key	Report Date	Name	AUM	YoY Change
123	20161231123	31-12-2016	Big Co	1000	0
123	20171231123	31-12-2017	Big Co	1200	200
123	20181231123	31-12-2018	Big Co	1450	250
456	20161231456	31-12-2016	Small Co	100	0
456	20171231456	31-12-2017	Small Co	110	10
456	20181231456	31-12-2018	Small Co	115	5