Summarised table by dates - Max date not quite right

Question

Hello!&nbsp;&nbsp;I have an existing table with customers, they all have one unique tenancy reference but each customer has multiple entries under different jobs. These jobs have unique job numbers and all have a different "logged dates"Example&nbsp;&nbsp;Tenancy ref: 0000123 appears 5 times, each with a different "date logged" date.&nbsp;&nbsp;I would like to summarise the data to see ONLY the LAST logged date (the most recent), so I do not want 5 lines, only one and the one should only return the date which is the most recent. All I need is the tenancy ref and the most recent logged date.&nbsp;&nbsp;I have set the table up and used the following code but it is returning the last date for ALL the data, not the last date for each customer.&nbsp;&nbsp;What do I need to fix to make this return the last date at each tenancy ref level and not the last date of the data set level.&nbsp;&nbsp;Any help much appreciated!&nbsp;&nbsp;Last CRM = SELECTCOLUMNS(ADDCOLUMNS(SUMMARIZE('OpenHousing dbo_re_tenancy','OpenHousing dbo_re_tenancy'[tenancy_ref],"Max Date", MAX ('OpenHousing dbo_em_communication'[logged_date])),"Date", [Max Date]), "Tenancy ref", 'OpenHousing dbo_re_tenancy'[tenancy_ref],"Date", [Date])&nbsp;&nbsp;THANK YOU! 🙂&nbsp;&nbsp;

anonymous · Answer

Hi,table.buffer function on sorting the date and removing duplicates on Tenancy ref column could help.&nbsp;You can check out this video, for an in depth explanation.&nbsp;https://www.google.com/search?q=power+query+how+to+sort+by+newest+date+then+remove+older+duplicates&amp;rlz=1C1GCEB_enUS1001US1001&amp;oq=power+query+how+to+sort+by+newest+date+then+remove+older+duplicates&amp;aqs=chrome..69i57.35026j1j7&amp;sourceid=chrome&amp;ie=UTF-8#fpstate=ive&amp;vld=cid:ee78921d,vid:S3X_HK7yl1wBR

johnt75 · Answer

Try
Last CRM =
SELECTCOLUMNS (
    SUMMARIZE (
        'OpenHousing dbo_re_tenancy',
        'OpenHousing dbo_re_tenancy'[tenancy_ref]
    ),
    "Tenancy ref", 'OpenHousing dbo_re_tenancy'[tenancy_ref],
    "Date", CALCULATE ( MAX ( 'OpenHousing dbo_re_tenancy'[logged_date] ) )
)

anonymous · Answer

Hi, thanks, I've tried that but it's still returning the same date for each as opposed to the individual last date per tenancy.

The tenancy and the logged date are on different tables - (re_tenancy and dbo_em_communication) is this causing the issue?

johnt75 · Answer

For that to work there would need to be a relationship between the two tables so that tenancy filters communications.

anonymous · Answer

Well this is embarassing&nbsp;😐That makes sense.&nbsp;&nbsp;I have an issue the communication table does not hold ANY identifying for the tenancy / customer. It has a unique code for the job but I have to use a third table which links that code to the tenancy reference and then pick the tenancy reference up from the tenancy table. So 3 tables in total and I cant do a direct link between the two i need.&nbsp;&nbsp;Other than creating a new SQL dim table, can I do something in desktop to create a table with the relevant columns from all 3 tables to combine them all in one place and then summarise? Long winded but would it work in desktop that way?&nbsp;

Forum Discussion

Summarised table by dates - Max date not quite right

6 Replies

Related content

Grouping and summarising

Can't linked a summarised table

Summarised table and slicer not working

Compare dates in a summarised table

Summarise