Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Latest Date

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! H...
Need Help
Anonymous's avatar
Anonymous
Not applicable
7 years ago

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's avatar
Anonymous
Not applicable
7 years ago

Hey Anonymous,
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.

 

ERPo18/lan^Ce^9/01/2019 
ERPoOOH-16^Ch^16/06/20161/03/2019

 

 

 

ERPo13/lan^Ce^13/03/2016 
ERPo18/lan^Ce^9/01/2019 
ERPoMy 017^Ce^10/01/2017 
ERPoOOH-16^Ch^16/06/20161/03/2019

 

 FULL Dummy data:

 

NameDoc NameCategoryPublish DateReview Date
RTKy201yia^Ce^2/10/20182/10/2018
RTKy201yia^Ct^21/01/201921/01/2020
SYKy201pdf^Ch^22/01/2019 
SYKy201pdf^Ch^22/01/2019 
SYKy201pdf^Cl^13/12/2018 
SYKy201pdf^Ch^22/01/2019 
SYKy201pdf^Ct^13/12/20183/04/2019
SYKy201ocx^CP^13/12/201812/04/2019
SYKy201pdf^Ch^13/12/201821/05/2019
SYKy201pdf^Ce^13/12/201819/09/2019
SYKy201ocx^Cs^13/12/2018 
SYKy201ocx^Cs^22/01/2019 
ASLa201doc^Ct^12/12/2018 
ONLa201sha^Cl^11/07/2018 
ONLa201pdf^CP^11/07/2018 
ONLa201sha^Ce^11/07/201819/04/2019
ONLa201sha^Ct^11/07/201830/04/2019
ONLa201sha^Ch^11/09/20187/05/2019
ONLa201sha^Ch^20/11/2018 
WELa201ren^Ch^20/11/2017 
WELa201pdf^Ce^23/01/201823/01/2018
WELa201pdf^CP^7/03/20187/03/2019
WELa201doc^Ct^21/03/201821/03/2019
WELa201ocx^Cl^5/09/20185/09/2019
ERLiFaC016^Ce^13/07/2016 
ERLiFaC017^Ce^18/05/2017 
ERLiFaC018^Ce^18/01/2018 
ERLo201ocx^Cl^31/01/20196/10/2018
ERLo201pdf^Ch^31/01/201923/01/2019
ERLo201pdf^Ce^31/01/201914/05/2019
ERLo201ocx^CP^31/01/201930/10/2019
ERLo201doc^Ct^31/01/201916/01/2020
GHMa201pdf^Ce^8/06/20188/06/2019
GEMa201ari^Cl^11/07/2018 
GEMa201ari^Ch^4/10/201815/02/2019
GEMa201ari^Cl^21/09/201821/02/2019
GEMa201ari^Ce^11/07/201823/04/2019
GEMa201ari^Ct^11/07/201824/04/2018
GEMa201pdf^Cl^14/12/201816/10/2019
GEMa201ari^Cl^25/01/201916/10/2019
GSMe201pdf^Cl^10/12/2018 
GSMe201pdf^Ce^10/12/2018 
GSMe201pdf^CP^10/12/2018 
GSMe201doc^Cl^10/12/2018 
GSMe201pdf^Ce^20/08/2018 
GSMe201doc^Ct^10/12/2018 
TEMi201pdf^Ce^3/09/2018 
TEMi201pdf^Ct^28/09/201828/03/2019
GENa201ley^Cl^11/07/2018 
GENa201ley^En^11/07/2018 
GENa201ley^Ch^4/10/201815/02/2019
GENa201ily^CP^31/10/2018 
GENa201ley^Ce^11/07/201823/04/2019
GENa201ley^Ct^11/07/201830/04/2019
GENa201ley^Cl^14/12/201816/10/2019
GENa201ley^Cl^25/01/201916/10/2019
THNi201ocx^Cl^30/11/2017 
THNi201doc^Ct^21/01/2019 
THNi201doc^Ce^12/11/201812/11/2019
ttNi201non^Cl^12/07/2018 
ttNi201non^CS^12/07/201824/10/2018
ttNi201non^Ch^5/12/201812/02/2019
ttNi201non^Ce^12/07/201821/02/2019
ttNi201non^CP^1/11/2018 
ttNi201non^Ct^12/07/201830/04/2019
ttNi201non^CP^12/07/201814/08/2018
ttNi201non^CP^5/09/201816/11/2018
ttNi201non^CP^5/12/201816/02/2019
ttNi201non^CS^19/12/201811/12/2019
NSOm201ion^Ct^11/07/201811/01/2019
NSOm201ion^Ce^11/07/201819/04/2019
NSOm201ion^CP^11/07/20188/08/2018
NSOm201ion^CS^12/07/201818/06/2019
NSOm201ion^CP^5/09/201816/11/2018
NSOm201ion^CP^4/12/2018 
ENPh201pdf^CP^29/08/2018 
ENPh201ocx^Cl^12/12/2018 
ENPh201pdf^Ce^24/08/201824/08/2019
ENPh201pdf^Ct^29/08/2018 
ERPo13/lan^Ce^13/03/2016 
ERPo18/lan^Ce^9/01/2019 
ERPoMy 017^Ce^10/01/2017 
ERPoOOH-16^Ch^16/06/20161/03/2019
  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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] )
    )
))
    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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