How can I get the latest value for each company based on a reporting date?

[rile14]

Hi All,

I'm trying to write a DAX that will return the latest value for each company, based on a specific reporting date.

Each company has multiple transactions with different transaction dates. Some of them have a transaction date that is equal to the reporting date, but some of them have the latest transaction date earlier. I want to get all the latest values and the correct total in the table. I can return all the latest values, but the total is not correct.

Here is the dataset:

Reporting date is 31/12/2021, and the expected result is 6.500.951.

Can anyone assist me with this,  please?

Thanks.

[rile14 · Accepted solution]

I've figured it out. 

First I have created this measure:

Transaction Value =
VAR maxasAtDate =
    MAX ( 'As At Date'[Date] )
VAR maxrepDt =
    MAX ( 'Date'[Date] )
VAR maxdt =
    MIN ( maxasAtDate, maxrepDt )
VAR maxTrxDate =
    CALCULATE ( MAX ( 'Test File'[Transaction Date] ), 'Date'[Date] <= maxdt )
RETURN
    CALCULATE (
        SUM ( 'Test File'[Value] ),
        ALL ( 'Date' ),
        'Date'[Date] >= maxTrxDate,
        'Date'[Date] <= maxdt
    )

And then another one to get the total amount correctly displayed in a table:

 Transaction Value (Total) =
SUMX ( SUMMARIZE ( 'Test File', Dim_Company[Company] ), [Transaction Value] )

[rile14]

 https://files.fm/u/5ftwwesg5  Try this one

@Mahesh0016 

[Mahesh0016]

@rile14 Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.