Forum Discussion

Javierco's avatar
Javierco
Helper I
4 years ago
Solved

Take latest data conditional on source

I have two sources of data (suppliers) that update product inventory data on different schedules. I would like know how much inventory is available (latest read).   Input data looks like this:   ...
  • v-cazheng-msft's avatar
    v-cazheng-msft
    4 years ago

    Hi Javierco 

     

    You may try this Measure.

    SumOfLatestRecords =

VAR LatestDate =

    ADDCOLUMNS (

        'Table',

        "latest", CALCULATE ( LASTDATE ( 'Table'[date] ), ALLEXCEPT ( 'Table', 'Table'[source] ) )

    )

RETURN

    CALCULATE (

        SUM ( 'Table'[qty] ),

        FILTER ( LatestDate, 'Table'[date] = [latest] )

    )

     

    Then, the result will look like this.

     

    Also, attached the pbix file as reference.

     

    Best Regards,

    Community Support Team _ Caiyun

     

    If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!

tamerj1's avatar
tamerj1
Community Champion
4 years ago

Hi Javierco 
Here is a sample file that contains the solution https://www.dropbox.com/t/G4SVVdi5t5w5tlMS

 

Basically, you need to create a new calculated column that retrieves the last order date per supplier for each record:

Last Date = 
MAXX (
    FILTER (
        Data,
        Data[source] = EARLIER ( Data[source] )
    ),
    Data[date]
)


Then you can create a simple measure that performs SUMX over a filtered table:

Last Date Qty = 
VAR LastDateTable = 
    FILTER ( 
        Data,
        Data[date] = Data[Last Date]
    )
VAR Result =
    SUMX (
        LastDateTable,
        Data[qty]
    )
RETURN
    Result