How to return the value of a other higher-valued column?

[leonardorm]

I have 3 tables: iteracao; trabalho; trabalhofoto (log: snapshot (daily)).

I need the sum of the "esforço" values of the largest data in the "trabalhofoto" table of data in the "trabalho" table where the data must be less than or equal to the final data ("datefim") of the work. "iteracao".

Using the sql below I get the result I need (last image), but how do I do this using DAX?

SELECT *,
    (SELECT SUM((SELECT tf.esforco
        FROM trabalhofoto tf
        WHERE t.trabalhoId = tf.trabalhoId AND
             tf.data <= i.dataFim
        ORDER BY tf.data DESC
        LIMIT 1)) as esforcoTotal
    FROM trabalho t) esforcoTotal
FROM iteracao i

iteracao.csv  ( http://dontpad.com/leonardorm/powerbi/iteracao )

Iteracao

trabalho.csv (http://dontpad.com/leonardorm/powerbi/trabalho)

Trabalho

trabalhofoto.csv (http://dontpad.com/leonardorm/powerbi/trabalhofoto)

TrabalhoFoto (daily snapshot)

Expected result

[TeigeGao · Accepted solution]

Hi leonardorm,

According to your description, my understanding is that you want to calculate the sum of esforco whose data is max in each trabahoId group and less than the dataFim.

In this scenario, we can use the following DAX query:

Measure =
SUMX (
    SUMMARIZE ( trabalhofoto, trabalho[trabalhoId] ),
    CALCULATE (
        MAXX (
            TOPN (
                1,
                FILTER ( trabalhofoto, trabalhofoto[data] <= MIN ( iteracao[dataFim] ) ),
                trabalhofoto[data], DESC
            ),
            trabalhofoto[esforco]
        )
    )
)

The result will like below:

Best Regards,

Teige