Summarising Max Date and First Occurence of a Value

[Borghi]

Hi folks,

I am having some hard times with SUMMARIZE().

I have a dataset with 3 columns: ID, Date and Value

Original Dataset

I am trying to create a new table using the SUMMARIZE() function, as the following:

TopDate = SUMMARIZE('Static', 'Static'[ID], "MaxDate", MAX('Static'[Date]), "NewValue", FIRSTNONBLANK('Static'[Value], MAX('Static'[Date])))

It is returning the following dataset:

Actual Dataset

However, it was suppose to get the following:

ToBe Dataset

Anyone have an idea why am I getting the values from a sorted order, even if I am choosing FIRSTNONBLANK() from MAX('Static'[Date])?

Thanks,

[Zubair_Muhammad · Accepted solution]

HI @Borghi

Try using this

TopDate =
SUMMARIZE (
    'Static',
    'Static'[ID],
    "MaxDate", MAX ( 'Static'[Date] ),
    "NewValue", CALCULATE ( FIRSTNONBLANK ( 'Static'[Value], 1 ), LASTDATE ( 'Static'[Date] ) )
)

[mapr]

Thanks so much. It works fine!

[Borghi]

Hi @Zubair_Muhammad,

It worked!

Thanks a million!