Forum Discussion
How to display data based on a latest date?
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?
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" value2. 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
lateststatusRegards,SayaliIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.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 dataI am also attaching pbix file for your reference.
4 Replies
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" value2. 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
lateststatusRegards,SayaliIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.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 dataI am also attaching pbix file for your reference.
Thank you so much. It helped,:)
