Distinct Count of a value from the last updated date

[ggzmorsh]

I am working in a logistics company where we deliver the shipments to the the client. I have a simple report that has duplicate datas which i need to put in the dashboard and what i need is the value that was last updated from a date column/row on a slicer.

Table:

Date	Reference No	Country	Remarks
07/23/2019	AWB1111	CHINA	Pending
07/24/2019	AWB1111	CHINA	Delivered
07/24/2019	AWB1112	AMERICA	Pending
07/24/2019	AWB1113	AFRICA	Pending
07/24/2019	AWB1114	JAPAN	Pending
07/24/2019	AWB1115	SPAIN	Pending
07/24/2019	AWB1116	CANADA	Pending
07/24/2019	AWB1117	AMERICA	Pending
07/24/2019	AWB1118	CANADA	Pending
07/24/2019	AWB1119	CANADA	Pending
07/24/2019	AWB1120	AMERICA	Pending
07/24/2019	AWB1121	CHINA	Pending
07/25/2019	AWB1112	AMERICA	Delivered
07/25/2019	AWB1113	AFRICA	Delivered
07/25/2019	AWB1122	SPAIN	Pending
07/25/2019	AWB1123	AMERICA	Pending
07/25/2019	AWB1124	JAPAN	Pending
07/25/2019	AWB1125	JAPAN	Pending
07/25/2019	AWB1126	CHINA	Pending
07/25/2019	AWB1127	CHINA	Pending
07/25/2019	AWB1128	AMERICA	Pending

Where my report matrix/table would only give me the distinct count of the value(Reference No) from the latest or newest date on the slicer(Update Date). If i add my Remarks column it should not give me duplicates on both remarks as the result would only give me distinct count of the Reference No with its Remark on the maximum or newest date.

Count Matrix:

Where AMERICA has 1 Delivered and 5 Pending gave me a total of 6, AWB1112 has values on both 7/24 and 7/25 

it should not give me the count for the previous day if it also appears on a later date. AWB1111 1112 1113 shows up 2 times in different dates. 

[ggzmorsh]

I will get back to you when i test the data before the week ends. Thanks

[v-xuding-msft]

Hi @ggzmorsh ,

Could you tell me if your problem has been solved? If it is, kindly mark the helpful answer as a solution if you feel that makes sense. Welcome to share your own solution. More people will benefit from here.

Best Regards,

Xue

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ggzmorsh]

I did not know this would be useful, thanks.

Table = CALENDARAUTO()

I used the formula as per your sampleand it is only showing me the last date from the calendarauto if i referenced it to Table and if i referenced it from my table.

Column = MAX(Table1[Date])

From your Table1

My Table

And it would not allow me to download your sample.

[v-xuding-msft · Accepted solution]

Hi @ggzmorsh ,

I created a sample you can reference.

• create a calendar table as a slicer

Table = CALENDARAUTO()

• create a calculated column to get the maximum date 

Column = MAX(Table1[Date])

• create a measure

Measure = 
var a = CALCULATE(DISTINCTCOUNT(Table1[Reference No]),FILTER(Table1,'Table1'[Date]= Table1[Column] && Table1[Date] >= MIN('Table'[Date])&&Table1[Date] <= MAX('Table'[Date])))
var b = CALCULATE(DISTINCTCOUNT(Table1[Reference No]),FILTER(Table1,Table1[Date] = MAX('Table'[Date])))
return
IF(ISBLANK(b),a,b)

Here is my sample you can download.

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.