Forum Discussion

Javierco's avatar
Javierco
Icon for Helper I rankHelper 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!

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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