How to get the latest values of a column per each distinct value of another column in DAX

Question

I am eager to create a calculated column in Power BI using DAX. From my table in the form of

projects   valid_date    target
2M         03/24/2019    0.9
3D         03/25/2019    1
5K         03/25/2019    0.9
2M         03/26/2019    0.9
3D         03/26/2019    0.5
5K         03/24/2019    0.6
...        ...           ...

I look to generate a cloumn that holds the value for target, but only if it is the one of the latest "valid_date" for each distinct project.

The result ("latest_target") should look like this:

projects   valid_date    target    latest_target
2M         03/24/2019    0.9       NULL
3D         03/25/2019    1         NULL
5K         03/25/2019    0.9       0.9
2M         03/26/2019    0.9       0.9
3D         03/26/2019    0.5       0.5
5K         03/24/2019    0.6       NULL
...        ...           ...       ...

What would the DAX formula look like for such a column? I have already tried out some formulas, but as I lack translating my T-SQL ideas into DAX effectively, I don't think it would be helpful to share these here.

Take a look at an answer I provided for a different question. You should be able to use the same logic here, just changing a bit of code. — Joe Gravelyn

RADO RADO · Accepted Answer · 2019-04-02T18:52:41

Assuming that your table name is "My_Table":

Latest Target =
   VAR 
     Latest_Date = CALCULATE(MAX(My_Table[valid_date]), ALLEXCEPT(My_Table[projects]))
   RETURN 
     IF(My_Table[valid_date]=Latest_Date, My_Table[target])

How it works: First, we find the latest (max) date per each "projects". ALLEXCEPT allows us to do that - for each record, we see the entire table filtered by this record "project". - then, by comparing max date and the record's date, we find if it's latest.

Make sure to adjust the number of decimal places for the new column according to your needs as it might be set to 0 by default.

How to get the latest values of a column per each distinct value of another column in DAX

1 Answers