Summarised table by dates - Max date not quite right

[Googlecanthelp]

Hello! 

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 

Tenancy ref: 0000123 appears 5 times, each with a different "date logged" date. 

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. 

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. 

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. 

Any help much appreciated! 

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])

THANK YOU! 🙂 

[johnt75]

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] ) )
)

[Googlecanthelp]

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]

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

[Googlecanthelp]

Well this is embarassing 😐

That makes sense. 

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. 

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? 

[johnt75]

You can move filters around using TREATAS, e.g.

Last CRM =
SELECTCOLUMNS (
    SUMMARIZE (
        'OpenHousing dbo_re_tenancy',
        'OpenHousing dbo_re_tenancy'[tenancy_ref]
    ),
    "Tenancy ref", 'OpenHousing dbo_re_tenancy'[tenancy_ref],
    "Date",
        VAR Jobs =
            CALCULATETABLE ( VALUES ( 'Jobs'[Job ID] ) )
        RETURN
            CALCULATE (
                MAX ( 'OpenHousing dbo_em_communication'[logged_date] ),
                TREATAS ( Jobs, 'OpenHousing dbo_em_communication'[Job ID] )
            )
)

This assumes that tenancy filters jobs and that there is a corresponding job ID column in communications

[Anonymous]

Hi,

table.buffer function on sorting the date and removing duplicates on Tenancy ref column could help. 
You can check out this video, for an in depth explanation. 

https://www.google.com/search?q=power+query+how+to+sort+by+newest+date+then+remove+older+duplicates&...

BR