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
axk180022
Helper II
Helper II

how to get the latest date and 2nd latest date in Power BI

axk180022_0-1629784248156.png

If I have the above table I would want to get just the latest date : 6/16/2021 and second latest date : 5/27/2021. 

 

can you please provide me solution.

13 REPLIES 13
VahidDM
Super User
Super User

Hi @axk180022 

 

Try these measures:

 

latest date = max('Table'[CALCULATIONDATETIME])
 
2nd latest = MAXX(FILTER('Table','Table'[CALCULATIONDATETIME]<MAX('Table'[CALCULATIONDATETIME])),'Table'[CALCULATIONDATETIME])
 
Example: 
VahidDM_0-1629785419362.png

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

Appreciate your Kudos VahidDM_1-1629785432636.png !!

Hi Vahid,

 

I want them in the same column, just sort the last and secondlast date from calculationdatetime column. so basically column should have only 2 dates.

 

@VahidDM 

Hi @axk180022 

Try this measure to add a column to your table:

2 LAST DATES = 
vAR _latestdate = max('Table'[CALCULATIONDATETIME])
vAR _2ndlatest = MAXX(FILTER('Table','Table'[CALCULATIONDATETIME]<MAX('Table'[CALCULATIONDATETIME])),'Table'[CALCULATIONDATETIME])
RETURN
IF('Table'[CALCULATIONDATETIME] IN {_latestdate,_2ndlatest},'Table'[CALCULATIONDATETIME])

 

the output will be as below:

 

VahidDM_0-1629848821998.png

 

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

Appreciate your Kudos VahidDM_1-1629848840998.png !!

Sorry for the confusion, I would be needing the last 2 dates for each inventserialid, so table shows just CVS-002, but there are other ids as well, so for each of the ids I would be requiring last 2 dates. 

 

@VahidDM 

Hi @axk180022 

 

Try this measure:

2 LAST DATES = 
Var _invent = FIRSTNONBLANK('Table'[INVENTSERIALID],"")
vAR _latestdate = CALCULATE(max('Table'[CALCULATIONDATETIME]),ALLEXCEPT('Table','Table'[INVENTSERIALID]))
vAR _2ndlatest = MAXX(FILTER(filter('Table','Table'[INVENTSERIALID]=_invent),'Table'[CALCULATIONDATETIME]<_latestdate),'Table'[CALCULATIONDATETIME])
RETURN
IF('Table'[CALCULATIONDATETIME] IN {_latestdate,_2ndlatest},'Table'[CALCULATIONDATETIME])

 

the output will be as below:

VahidDM_0-1629855964379.png

 

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

Appreciate your Kudos VahidDM_1-1629855975383.png !!

 

@axk180022 

 

Can you pleae share the sample file here.

Table with different "inventserialid" or Power BI file.

axk180022_0-1629855418795.png

 

Here is the table 

 

@VahidDM 

Hi @axk180022 

 

Try this measure:

 

 

2 LAST DATES = 
Var _invent = FIRSTNONBLANK('Table'[INVENTSERIALID],"")
vAR _latestdate = CALCULATE(max('Table'[CALCULATIONDATETIME]),ALLEXCEPT('Table','Table'[INVENTSERIALID]))
vAR _2ndlatest = MAXX(FILTER(filter('Table','Table'[INVENTSERIALID]=_invent),'Table'[CALCULATIONDATETIME]<_latestdate),'Table'[CALCULATIONDATETIME])
RETURN
IF('Table'[CALCULATIONDATETIME] IN {_latestdate,_2ndlatest},'Table'[CALCULATIONDATETIME])

 

 

 

the output will be as below:

VahidDM_0-1629855964379.png

 

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

Appreciate your Kudos VahidDM_1-1629855975383.png !!

 

Hi,

 

i got this error when i tried to create the column

 

"DAX comparison operations do not support comparing values of type Text with values of type Date. Consider using the VALUE or FORMAT function to convert one of the values."

 

@VahidDM 

@axk180022 

It works well on my computer! Make sure the "CALCULATIONDATETIME" column format is Date.

Is it possible to share your file with me?

 

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

Appreciate your Kudos VahidDM_0-1629867530887.png !!

Since im new to forum im unable to upload power bi file here. So the inventserialid is text, seems like that is causing problem.  CALCULATIONDATETIME is in date format already

It seems everything is OK!

 

Could you please share your file on https://gofile.io/ 

and share the link here.

I tried the formula and it worked, but for few it shows blank or doesnt pick up the 2nd latest date.

 

@VahidDM 

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.