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!

ValtteriN's avatar
ValtteriN
Icon for Community Champion rankCommunity Champion
4 years ago

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/