Get most recent values on new table column

[satlasg]

Hello, 

I have two tables A & B with "many to one" relationship, as below:

Table A              Table B
I am looking for a formula to fill in 'Table B'[Value Latest] based on the Value from the more recent Date the ID has from Table A.
Any ideas please?

[satlasg]

@nickchobotar

I am getting the following when using the formula you provided as Column:

---

Please try this option. Works both as calc column and measure.

ColumnName = 
CALCULATE(
    VALUES(Table1[Value]),
    LASTDATE( Table1[Date])
)

 

---

[satlasg]

@nickchobotar you are right, apologies.

However, i have tested your code and that is where i ended:

---

@satlasg wrote:

@nickchobotar

I am getting the following when using the formula you provided as Column:

---

Please try this option. Works both as calc column and measure.

ColumnName = 
CALCULATE(
    VALUES(Table1[Value]),
    LASTDATE( Table1[Date])
)

---

---

I also checked the IDs and there are no duplicates., cannot figure where the "single value was expected" comes from.

Thanks for the pbix file.

Will try to implement the M code and see where it ends.

G

[nickchobotar]

@satlasg

Please make sure you have no ID duplicates in your Table B( Table 2)

Thanks, N -

[satlasg]

@nickchobotar

There are no ID duplicates in Table B (Table 2), i double-checked through Count and Distinct Count, they have the same number as a result. So that is not the issue.

Keep looking around, thanks.

[nickchobotar]

@satlasg

Are you relating both tables on the ID field  or you have other keys ?

Any chance, we can see the model diagram ?

N -

[Zubair_Muhammad]

@satlasg

In that case use FirstNonBlank instead of SUM. Here is the revised formula

Value latest =
VAR RecentDate =
    CALCULATE ( MAX ( TableA[Date] ) )
RETURN
    CALCULATE (
        FIRSTNONBLANK ( TableA[Value], 1 ),
        FILTER ( TableA, TableA[Date] = RecentDate )
    )

[satlasg]

Hi @Zubair_Muhammad

Tried that revised one, but again it seems to drain the memory, i am getting the same message and no result.

---

Value latest =
VAR RecentDate =
    CALCULATE ( MAX ( TableA[Date] ) )
RETURN
    CALCULATE (
        FIRSTNONBLANK ( TableA[Value], 1 ),
        FILTER ( TableA, TableA[Date] = RecentDate )
    )

---

[Zubair_Muhammad]

@satlasg

Try this column

Value latest =
VAR RecentDate =
    CALCULATE ( MAX ( TableA[Date] ) )
RETURN
    CALCULATE (
        SUM ( TableA[Value] ),
        FILTER ( TableA, TableA[Date] = RecentDate )
    )

[satlasg]

Hi @Zubair_Muhammad, i tried it

---

Value latest =
VAR RecentDate =
    CALCULATE ( MAX ( TableA[Date] ) )
RETURN
    CALCULATE (
        SUM ( TableA[Value] ),
        FILTER ( TableA, TableA[Date] = RecentDate )
    )

---

but while in the provided sample tables it semmes to bring the right answer, in me data the results are kind of awkward. The values for example appear in TableB with digits and thy are not correct. I tried replacing the SUM in your formula, with VALUE but then the formula takes ages to run (Working on it) since i have >500K rows in my data, and at the end i am getting the error: "Memory insufficient, please try again later"

 Any workaround please?

[filipe197]

This is just a test