sum of most recent values

[Anonymous]

Hi All,

I have a spreadsheet that is released bi weekly from a contractor. on the spreadsheet is the number of referrals made to a health programme i run by GP practice. 

As the sheets come in im adding a column in PBI for date submitted, in this case i have two seperate spreadhseets, one dated the 2nd feb 18 and one dated 14th feb 18. i appended the later one to the bottom of the earlier one

the spreadsheets are presummarized numbers by practice each representing one date. every time I append a new one i need my measures to show only the most recent date.

I cant seem to figure out how to do it - i know its something to do with LASTDATE

[v-yulgu-msft · Accepted solution]

Hi @Anonymous,

Please refer to below formula:

most recent sum =
CALCULATE (
    SUM ( 'Table'[referrals] ),
    FILTER (
        'Table',
        'Table'[Date] = CALCULATE ( MAX ( 'Table'[Date] ), ALL ( 'Table' ) )
    )
)

Replace the table name and column name with the actual ones in your scenario.

Best regards,

Yuliana Gu

[Anonymous]

Thank-you - thats really helpful 🙂