Highest Value within last 3 years

[ariana_night]

Hello,

I am trying to write a measure that will bring me the qty of "152,027,444" shown in the below table visual since this is the highest the inventory level has been in the last 3 years. The table was filtered using relative date filter in filter panel. 

Why? because in another measure I will need to divide the qty of "145,590,538" shown below with the qty above to show end users the % difference between how much is on hand vs our highest level recorded within the last 3 years, in a card visual. 

Qty is 'Excess Inventory' [Excess Inventory Total Gross Inventory]

Date is 'Excess Inventory' [Report Date] - you can see this is recorded monthly

I was successful with getting the qty of "145,590,538" through this measure: (But need help with the other qty)

CALCULATE(SUM('Excess Inventory'[TotalGrossInv]), LASTDATE('Excess Inventory'[Report Date]))

Please let me know if you need additional information.

Best,

Ariana

[ariana_night]

For the purpose of helping others with similar inquiry:

1. I am connect to a live database, therefore I am only able to create new measures (nothing else, unless my request is significant enough to ask business development to create a new table/column on the back end).

2. There is already a calendar table in my database called Report Date that updates on a monthly basis, so I did not need to create a new calendar table 

3. The most useful measures for the purposes of my report that Jihwan suggested were: Qty measure, Top Qty Recent Three Years, and Qty Ratio vs Highest in Three Years - see my specific measures below

qty measure = 
    IF(
        HASONEVALUE('Excess Inventory'[Report Date]), SUM('Excess Inventory'[TotalGrossInv]))

top qty recent three years: = 
VAR _recentthreeyears =
    SUMMARIZE (
        CALCULATETABLE (
            'Excess Inventory',
            DATESINPERIOD ( 'Excess Inventory'[Report Date], LASTDATE ('Excess Inventory'[Report Date]), -3, YEAR )
        ),
        'Excess Inventory'[Report Date]
    )
VAR _addmonthlyqty =
    ADDCOLUMNS ( _recentthreeyears, "@monthlyqty", [qty measure] )
RETURN
    MAXX ( _addmonthlyqty, [@monthlyqty] )

qty ratio vs highest in three years = 
VAR _currentqty =
    CALCULATE (
        [qty measure],
        'Excess Inventory'[Report Date] = EOMONTH ( MAX ( 'Excess Inventory'[Report Date] ), 0 )
    )
RETURN
    DIVIDE ( _currentqty, [top qty recent three years:] )

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

I tried to create a sample pbix file like below, and all measures are in the attached file.

[ariana_night]

This has helped - thank you so much!