Dax measure calculating the evolution on the last 2 submissions/dates

[julienvdc]

Hey,

I have this table that lists percentages (value) for a bunch of recurring fields (field_id). I would like to build a mesaure that evaluated how those fields are evolving but only on the last 2 submissions/import. You can see we have information on the import date/time and each import has a dedicated file_id. So i think there is something to be done on either of these columns but I am not sure how to approach this.

I can easily find out the evolution across all imports, but the last 2 ... I am not sure... Any thoughts?

[v-jiewu-msft]

Hi @julienvdc ,

Please try the following methods and check if they can solve your problem:

1.Create the new measure to identify the last two.

Last Two Imports = 
RANKX(
    ALL('Table'[imported_on]), 
    CALCULATE(MAX('Table'[imported_on])), 
    ,
    DESC, 
    Dense
)

2.Create the new measure to calculate the value for each field_id. Enter the following DAX formula.

ValueLastImports = 
VAR LastImportValue = CALCULATE(
    SUM('Table'[value]), 
    FILTER(
        'Table', 
        [Last Two Imports Rank] = 1
    )
)
VAR SecondLastImportValue = CALCULATE(
    SUM('Table'[value]), 
    FILTER(
        'Table', 
        [Last Two Imports Rank] = 2
    )
)
RETURN
IF(
    NOT(ISBLANK(SecondLastImportValue)),
    (LastImportValue - SecondLastImportValue) / SecondLastImportValue,
    BLANK()
)

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.