Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Borghi
Advocate I
Advocate I

Summarising Max Date and First Occurence of a Value

Hi folks,

 

I am having some hard times with SUMMARIZE().

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

Original DatasetOriginal 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 DatasetActual Dataset

However, it was suppose to get the following:

ToBe DatasetToBe 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,

1 ACCEPTED SOLUTION
Zubair_Muhammad
Community Champion
Community Champion

HI @Borghi

 

Try using this

 

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

Regards
Zubair

Please try my custom visuals

View solution in original post

3 REPLIES 3
Zubair_Muhammad
Community Champion
Community Champion

HI @Borghi

 

Try using this

 

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

Regards
Zubair

Please try my custom visuals

Thanks so much. It works fine!

Hi @Zubair_Muhammad,

 

It worked!

 

Thanks a million! Smiley Happy

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.