Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Sagun
New Member

How to display data based on a latest date?

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

 

IDForm Completion time Shop open/close date Name of  BranchStatus
215/4/21 10:04:275/5/2021GongabuOpen
225/4/21 10:04:565/5/2021MaharajgunjClose
235/4/21 10:27:165/5/2021SwoyambhuOpen
245/4/21 10:28:285/5/2021ThamelOpen
255/4/21 11:35:565/5/2021GongabuClose
265/4/21 10:36:245/5/2021ThamelClose
275/4/21 11:36:485/5/2021MaharajgunjOpen
285/4/21 10:47:175/5/2021ChyamasinghOpen

 

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:

 

IDForm Completion timeShop open/close dateName of  BranchStatus
235/4/21 10:27:165/5/2021SwoyambhuOpen
255/4/21 11:35:565/5/2021GongabuClose
265/4/21 10:36:245/5/2021ThamelClose
275/4/21 11:36:485/5/2021MaharajgunjOpen
285/4/21 10:47:175/5/2021ChyamasinghOpen

 

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?

2 ACCEPTED SOLUTIONS
sayaliredij
Super User
Super User

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
sayaliredij_0-1620559028893.png

 

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.




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

negi007
Community Champion
Community Champion

@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.

 

negi007_0-1620560710974.png

 

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
 
negi007_1-1620560827829.png

 

 

I am also attaching pbix file for your reference.

 

 




Did I answer your question? Mark my post as a solution!
Appreciate your Kudos



Proud to be a Super User!


Follow me on linkedin

View solution in original post

4 REPLIES 4
Sagun
New Member

Thank you so much. It helped,:)

negi007
Community Champion
Community Champion

@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.

 

negi007_0-1620560710974.png

 

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
 
negi007_1-1620560827829.png

 

 

I am also attaching pbix file for your reference.

 

 




Did I answer your question? Mark my post as a solution!
Appreciate your Kudos



Proud to be a Super User!


Follow me on linkedin

sayaliredij
Super User
Super User

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
sayaliredij_0-1620559028893.png

 

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.




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hello @sayaliredij,

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

Jaggana_0-1701944240451.png

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.