How to display data based on a latest date?

[Sagun]

This is my data source named as "All Data":

ID	Form Completion time	Shop open/close date	Name of  Branch	Status
21	5/4/21 10:04:27	5/5/2021	Gongabu	Open
22	5/4/21 10:04:56	5/5/2021	Maharajgunj	Close
23	5/4/21 10:27:16	5/5/2021	Swoyambhu	Open
24	5/4/21 10:28:28	5/5/2021	Thamel	Open
25	5/4/21 11:35:56	5/5/2021	Gongabu	Close
26	5/4/21 10:36:24	5/5/2021	Thamel	Close
27	5/4/21 11:36:48	5/5/2021	Maharajgunj	Open
28	5/4/21 10:47:17	5/5/2021	Chyamasingh	Open

My Question:

For a certain "Shop open/close date", I want to display Name of a Branch and Status which is Based on a latest date in "Form completion time" column.

My desired output:

ID	Form Completion time	Shop open/close date	Name of  Branch	Status
23	5/4/21 10:27:16	5/5/2021	Swoyambhu	Open
25	5/4/21 11:35:56	5/5/2021	Gongabu	Close
26	5/4/21 10:36:24	5/5/2021	Thamel	Close
27	5/4/21 11:36:48	5/5/2021	Maharajgunj	Open
28	5/4/21 10:47:17	5/5/2021	Chyamasingh	Open

Steps I tried:

Trial 1: Measure = MAXX('All Data','All Data'[Form Completion time])

Trial 2: Measure = CALCULATE(COUNTA('All Data'[Status]),FILTER('All Data','All Data'[Form Completion time] = MAX('All Data'[Form Completion time])))

Trial 3: Measure = CALCULATE(COUNTA('All Data'[Status]),FILTER('All Data','All Data'[Form Completion time] = LASTDATE('All Data'[Form Completion time])))

But none of this works. Please suggest?

[sayaliredij · Accepted solution]

Hi @Sagun 

I would have 2 options to solve your problem

1. You can create a new calculated column Islatest which indicates if the current row is based on the latest value of the name of the branch

IsLatest = 

var Latestdate = CALCULATE(MAX('Table'[Form Completion time]),FILTER('Table','Table'[Name of Branch] = EARLIER('Table'[Name of Branch])))

RETURN 

IF('Table'[Form Completion time] = Latestdate,1,0)

and then you can filter your table based on "IsLatest" value

2. You can create measure as Latest Status using the following formule

Latest Status = 

var latestdate = CALCULATE(MAX('Table'[Form Completion time]),REMOVEFILTERS('Table'[Status]))

var lateststatus = LOOKUPVALUE('Table'[Status],'Table'[Form Completion time],latestdate)

RETURN

lateststatus

Regards,

Sayali

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

[Sagun]

Thank you so much. It helped,:)

[negi007 · Accepted solution]

@Sagun Hi sagun, in this case i created a summary table from the main table which i can use to show data in the matrix visual.

I created table which will extract the max date for each branch. then using lookup function we will get value from main table to summary table like below

Summary_Table = SUMMARIZE('Table','Table'[Name of Branch],"Max",MAX('Table'[Form Completion time ]))

Status = LOOKUPVALUE('Table'[Status],'Table'[Form Completion time ],'Summary_Table'[Form_Completion],'Table'[Name of Branch],Summary_Table[Name of Branch])

Branch = LOOKUPVALUE('Table'[Name of Branch],'Table'[Form Completion time ],'Summary_Table'[Form_Completion],'Table'[Name of Branch],Summary_Table[Name of Branch])

ID = LOOKUPVALUE('Table'[ID],'Table'[Form Completion time ],'Summary_Table'[Form_Completion],'Table'[Name of Branch],Summary_Table[Name of Branch])

Shope_Open_Closed = LOOKUPVALUE('Table'[Shop open/close date ],'Table'[Form Completion time ],'Summary_Table'[Form_Completion],'Table'[Name of Branch],Summary_Table[Name of Branch])

below is the visual from the main data

I am also attaching pbix file for your reference.

[sayaliredij · Accepted solution]

Hi @Sagun 

I would have 2 options to solve your problem

1. You can create a new calculated column Islatest which indicates if the current row is based on the latest value of the name of the branch

IsLatest = 

var Latestdate = CALCULATE(MAX('Table'[Form Completion time]),FILTER('Table','Table'[Name of Branch] = EARLIER('Table'[Name of Branch])))

RETURN 

IF('Table'[Form Completion time] = Latestdate,1,0)

and then you can filter your table based on "IsLatest" value

2. You can create measure as Latest Status using the following formule

Latest Status = 

var latestdate = CALCULATE(MAX('Table'[Form Completion time]),REMOVEFILTERS('Table'[Status]))

var lateststatus = LOOKUPVALUE('Table'[Status],'Table'[Form Completion time],latestdate)

RETURN

lateststatus

Regards,

Sayali

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

[Jaggana]

Hello @sayaliredij,

I have 2 errors using your code, do you have any idea why?