Latest Date

[Anonymous]

EDIT - 
Latest Date by 2 ID columns.
Is that possible?
I tried to concatenate the columns, but got an error "end of the input was reached"

Please look for examples in message below.
Ta!


Hi All,

I am trying to return the latest date with the built in function.

I have 2 tables with relationship. Where Table1.id = Table2.id

I want for each Table2.id return latest Table1.date.

It is not working for me as expected.

Any ideas?

Cheers!

[Anonymous]

I tried merging 2 tables but got different results and missing values in the columns.

Not sure why.

So I am trying to get latest date from T2 (Circle 3) according to T1,id (Circle1).

The merge table between Circle1 and Circle2 gives missing results than I have in the relationship and visuals created.

Cheers!

[v-shex-msft]

Hi @Anonymous,

You can try to add following calculate column to table 2 to get related table 1 id, then use these id to get correspond date and summary the max one:

LastDate =
CALCULATE (
    LASTNONBLANK ( Table1[Date], [Date] ),
    FILTER ( ALL ( Table1 ), Table1[ID] = EARLIER ( Table2[ID] ) )
)

Regards,

Xiaoxin Sheng

[Anonymous]

Hi @v-shex-msft

Table 2 is just a connection table.

All it conatins in IDs from table 1 and the related IDs from table 3.

So I dont think I need to get the IDs from the table(s), as they are already there.

I am still not able to filter and get the latest date record.

Cheers!

[v-shex-msft]

Hi @Anonymous,

If you can please share a simple sample with same data structure so that I can test on it.

Regards,

Xiaoxin Sheng

[Anonymous]

Hey @v-shex-msft,
Thanks for coming back.

Here is the data example in below table.

I have chnaged the data model, as the previous one had many issues.

Now I have 1 table with all relevant data in it.

I want to retrieve the latest (publish date) record for each person and category.

For example:
Name - ERPo
I want to show 2 records out of the 4 in the example.

^Ce^ will have 3 records, but showing only one with latest date.

^Ch^ will have only 1 records, so showing that single one.

ERPo

18/lan

^Ce^

9/01/2019

ERPo

OOH-16

^Ch^

16/06/2016

1/03/2019

ERPo	13/lan	^Ce^	13/03/2016
ERPo	18/lan	^Ce^	9/01/2019
ERPo	My 017	^Ce^	10/01/2017
ERPo	OOH-16	^Ch^	16/06/2016	1/03/2019

 FULL Dummy data:

Name

Doc Name

Category

Publish Date

Review Date

RTKy	201yia	^Ce^	2/10/2018	2/10/2018
RTKy	201yia	^Ct^	21/01/2019	21/01/2020
SYKy	201pdf	^Ch^	22/01/2019
SYKy	201pdf	^Ch^	22/01/2019
SYKy	201pdf	^Cl^	13/12/2018
SYKy	201pdf	^Ch^	22/01/2019
SYKy	201pdf	^Ct^	13/12/2018	3/04/2019
SYKy	201ocx	^CP^	13/12/2018	12/04/2019
SYKy	201pdf	^Ch^	13/12/2018	21/05/2019
SYKy	201pdf	^Ce^	13/12/2018	19/09/2019
SYKy	201ocx	^Cs^	13/12/2018
SYKy	201ocx	^Cs^	22/01/2019
ASLa	201doc	^Ct^	12/12/2018
ONLa	201sha	^Cl^	11/07/2018
ONLa	201pdf	^CP^	11/07/2018
ONLa	201sha	^Ce^	11/07/2018	19/04/2019
ONLa	201sha	^Ct^	11/07/2018	30/04/2019
ONLa	201sha	^Ch^	11/09/2018	7/05/2019
ONLa	201sha	^Ch^	20/11/2018
WELa	201ren	^Ch^	20/11/2017
WELa	201pdf	^Ce^	23/01/2018	23/01/2018
WELa	201pdf	^CP^	7/03/2018	7/03/2019
WELa	201doc	^Ct^	21/03/2018	21/03/2019
WELa	201ocx	^Cl^	5/09/2018	5/09/2019
ERLi	FaC016	^Ce^	13/07/2016
ERLi	FaC017	^Ce^	18/05/2017
ERLi	FaC018	^Ce^	18/01/2018
ERLo	201ocx	^Cl^	31/01/2019	6/10/2018
ERLo	201pdf	^Ch^	31/01/2019	23/01/2019
ERLo	201pdf	^Ce^	31/01/2019	14/05/2019
ERLo	201ocx	^CP^	31/01/2019	30/10/2019
ERLo	201doc	^Ct^	31/01/2019	16/01/2020
GHMa	201pdf	^Ce^	8/06/2018	8/06/2019
GEMa	201ari	^Cl^	11/07/2018
GEMa	201ari	^Ch^	4/10/2018	15/02/2019
GEMa	201ari	^Cl^	21/09/2018	21/02/2019
GEMa	201ari	^Ce^	11/07/2018	23/04/2019
GEMa	201ari	^Ct^	11/07/2018	24/04/2018
GEMa	201pdf	^Cl^	14/12/2018	16/10/2019
GEMa	201ari	^Cl^	25/01/2019	16/10/2019
GSMe	201pdf	^Cl^	10/12/2018
GSMe	201pdf	^Ce^	10/12/2018
GSMe	201pdf	^CP^	10/12/2018
GSMe	201doc	^Cl^	10/12/2018
GSMe	201pdf	^Ce^	20/08/2018
GSMe	201doc	^Ct^	10/12/2018
TEMi	201pdf	^Ce^	3/09/2018
TEMi	201pdf	^Ct^	28/09/2018	28/03/2019
GENa	201ley	^Cl^	11/07/2018
GENa	201ley	^En^	11/07/2018
GENa	201ley	^Ch^	4/10/2018	15/02/2019
GENa	201ily	^CP^	31/10/2018
GENa	201ley	^Ce^	11/07/2018	23/04/2019
GENa	201ley	^Ct^	11/07/2018	30/04/2019
GENa	201ley	^Cl^	14/12/2018	16/10/2019
GENa	201ley	^Cl^	25/01/2019	16/10/2019
THNi	201ocx	^Cl^	30/11/2017
THNi	201doc	^Ct^	21/01/2019
THNi	201doc	^Ce^	12/11/2018	12/11/2019
ttNi	201non	^Cl^	12/07/2018
ttNi	201non	^CS^	12/07/2018	24/10/2018
ttNi	201non	^Ch^	5/12/2018	12/02/2019
ttNi	201non	^Ce^	12/07/2018	21/02/2019
ttNi	201non	^CP^	1/11/2018
ttNi	201non	^Ct^	12/07/2018	30/04/2019
ttNi	201non	^CP^	12/07/2018	14/08/2018
ttNi	201non	^CP^	5/09/2018	16/11/2018
ttNi	201non	^CP^	5/12/2018	16/02/2019
ttNi	201non	^CS^	19/12/2018	11/12/2019
NSOm	201ion	^Ct^	11/07/2018	11/01/2019
NSOm	201ion	^Ce^	11/07/2018	19/04/2019
NSOm	201ion	^CP^	11/07/2018	8/08/2018
NSOm	201ion	^CS^	12/07/2018	18/06/2019
NSOm	201ion	^CP^	5/09/2018	16/11/2018
NSOm	201ion	^CP^	4/12/2018
ENPh	201pdf	^CP^	29/08/2018
ENPh	201ocx	^Cl^	12/12/2018
ENPh	201pdf	^Ce^	24/08/2018	24/08/2019
ENPh	201pdf	^Ct^	29/08/2018
ERPo	13/lan	^Ce^	13/03/2016
ERPo	18/lan	^Ce^	9/01/2019
ERPo	My 017	^Ce^	10/01/2017
ERPo	OOH-16	^Ch^	16/06/2016	1/03/2019

[Anonymous]

Tried using this solution - https://community.powerbi.com/t5/Desktop/Get-Max-Date-of-2-Columns/td-p/459071

Giving me an error

A single value for column 'Date' in table 'Table1' cannot be determined.
This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

Measure =
CALCULATE (
    MAX ( 'Table1'[Call Date] ),
    FILTER (
        ALL ( 'Table1' ),
        'Table1'[StoreNameEN] = MAX ( 'Table1'[StoreNameEN] )
            && 'Table1'[UPCUnit] = MAX ( 'Table1'[UPCUnit] )
    )
))

[v-shex-msft]

Hi @Anonymous,

You can try to use following measure, it will get the last date based on filtered table records and grouped by current category and name fields.

Measure =
CALCULATE (
    MAX ( T3[Publish Date] ),
    ALLSELECTED ( T3 ),
    VALUES ( T3[Name] ),
    VALUES ( T3[Category] )
)

Regards,

Xiaoxin Sheng

[Anonymous]

I believe that Power BI is not able to handle to many relationships.

There is a need to create tables with lookups and derive data from other tables.

Also, Power BI is limited with the queries inserted when loading the data from the sources. Making it harder to manage the entire model and reports.