Take latest data conditional on source

[Javierco]

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:

source	date	qty
supplier 1	1/1/2021	4
supplier 1	1/9/2021	1
supplier 2	1/12/2021	10

Desired output would be a measure with the sum of the latest records for supplier 1 and supplier 2: 11 units (10+1)

This one works well with just one supplier: 

calculate(sumX(inventory_v2, inventory_v2[inventory_amount] ,LASTDATE(sellout_v2[sales_date]))

Any help would be much appreciated

[v-cazheng-msft · Accepted solution]

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]

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 

 

[Anonymous]

Hi Javierco,

Please Try This measure And let me know your Answer.

My Measure =
VAR _latest =
       MAX ( 'Table'[Date] )
VAR _2nd_latest =
      MAXX ( FILTER ( 'Table', 'Table'[Date] < _latest ), 'Table'[Date] )
VAR _FilterTable =
       FILTER ( 'Table', 'Table'[Source] IN { "suppliar1", "suppliar2" } )
VAR _Result =
       CALCULATE (
              SUM ( 'Table'[qty] ),
              FILTER (
                  _FilterTable,
                  'Table'[Date] IN DATESBETWEEN ( 'Table'[Date], _2nd_latest, _latest )
              )
        )
RETURN
      _Result

[ValtteriN]

Hi,

Try something like this:

var _date = MAX('Calendar'[Date])
var _latestdate = CALCULATE(MAX('Table'[Date]),ALL('Table'[Date]),'Table'[Date]<=_date)
Return

CALCULATE(SUM('Table'[qty]),ALL('Table'[Date]),Table[Date]=_latestdate)

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/