calculate the change from latest date from earliest date from a table

[daltonrotork]

Hi - I have a table in Power BI like so

Company	Value	Month-Year
Head office	42	Jan 23
Head office	53	Jun 23
Head office	63	Dec 23
Factory	56	Jan 23
Factory	65	Jun 23
Factory	67	Dec 23

Etc

And I'm looking for DAX that would show the difference from the latest date in the table to the earliest date; so Dec 23 value - Jan 23 value, as a number and a percentage for each company - can anyone help me out with that?

Thanks!

[johnt75]

I think you could create a measure like

% diff =
VAR FirstValue =
    SELECTCOLUMNS (
        INDEX (
            1,
            'Table',
            ORDERBY ( 'Table'[Month-Year], ASC ),
            PARTITIONBY ( 'Table'[Company] ),
            MATCHBY ( 'Table'[Month-Year], 'Table'[Company] )
        ),
        "@value", 'Table'[Value]
    )
VAR LastValue =
    SELECTCOLUMNS (
        INDEX (
            1,
            'Table',
            ORDERBY ( 'Table'[Month-Year], DESC ),
            PARTITIONBY ( 'Table'[Company] ),
            MATCHBY ( 'Table'[Month-Year], 'Table'[Company] )
        ),
        "@value", 'Table'[Value]
    )
VAR Result =
    DIVIDE ( LastValue - FirstValue, FirstValue )
RETURN
    Result

[some_bih]

Hi @daltonrotork  measure could be:

Range as measure:
VAR _max_date=MAX(<Your table>[Month-Year])

VAR _min_date=MIN(<Your table>[Month-Year])
RETURN 
CALCULATE( SUM( <Your table>[Value]),

_max_date-  _min_date)

Did I answer your question? Mark my post as a solution! Kudos Appreciated!