cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sridharpolina
Helper I
Helper I

Creating a calculated column for the latest and previous date based on another column

I have couple of columns in my table with which I am creating a As of Date column from the date column which outputs the values as latest,previous or the date column values for others. But I need to have a calculated column where the top two dates where the flg = p should have the latest and previous and all the other should have the date column values as shown in the image. Thanks for all the help.

 

Image2.jpg

DateAs of DateflgNeeded Column
6/24/2021 6:00LatestPLatest
6/23/2021 19:00PreviousA6/23/2021 19:00
6/23/2021 16:006/23/2021 16:00A6/23/2021 16:00
6/23/2021 14:006/23/2021 14:00A6/23/2021 14:00
6/23/2021 7:006/23/2021 7:00PPrevious
6/22/2021 19:006/22/2021 19:00P6/22/2021 19:00
6/22/2021 16:006/22/2021 16:00A6/22/2021 16:00
6/22/2021 7:006/22/2021 7:00P6/22/2021 7:00
6/21/2021 19:006/21/2021 19:00P6/21/2021 19:00
6/21/2021 14:006/21/2021 14:00A6/21/2021 14:00
6/21/2021 7:006/21/2021 7:00P6/21/2021 7:00
6/20/2021 19:006/20/2021 19:00A6/20/2021 19:00
6/19/2021 19:006/19/2021 19:00A6/19/2021 19:00
6/18/2021 16:006/18/2021 16:00A6/18/2021 16:00
6/18/2021 12:006/18/2021 12:00A6/18/2021 12:00
6/18/2021 11:006/18/2021 11:00A6/18/2021 11:00
6/18/2021 6:006/18/2021 6:00P6/18/2021 6:00
6/17/2021 19:006/17/2021 19:00P6/17/2021 19:00
6/17/2021 17:006/17/2021 17:00A6/17/2021 17:00
6/17/2021 9:006/17/2021 9:00P6/17/2021 9:00
1 ACCEPTED SOLUTION
v-angzheng-msft
Community Support
Community Support

Hi, @sridharpolina 

 

Try to create 2 columns like this:

_Rank_P =
VAR _rank =
    RANKX ( FILTER ( ALL ( 'Table' ), 'Table'[flg] = "P" ), [Date],, DESC, DENSE )
VAR _isP =
    IF ( [flg] = "P", _rank, BLANK () )
RETURN
    _isP
_Need Column =
SWITCH (
    TRUE (),
    'Table'[_Rank_P] = 1, "Lastest",
    'Table'[_Rank_P] = 2, "Previous",
    FORMAT ( 'Table'[Date], "General Date" )
)

 Result:

vangzhengmsft_0-1625030948422.png

Please refer to the attachment below for details

 

Hope this helps.

 

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
v-angzheng-msft
Community Support
Community Support

Hi, @sridharpolina 

 

Try to create 2 columns like this:

_Rank_P =
VAR _rank =
    RANKX ( FILTER ( ALL ( 'Table' ), 'Table'[flg] = "P" ), [Date],, DESC, DENSE )
VAR _isP =
    IF ( [flg] = "P", _rank, BLANK () )
RETURN
    _isP
_Need Column =
SWITCH (
    TRUE (),
    'Table'[_Rank_P] = 1, "Lastest",
    'Table'[_Rank_P] = 2, "Previous",
    FORMAT ( 'Table'[Date], "General Date" )
)

 Result:

vangzhengmsft_0-1625030948422.png

Please refer to the attachment below for details

 

Hope this helps.

 

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Jihwan_Kim
Super User
Super User

Picture1.png

 

Needed Column CC =
VAR toptwotableflagP =
TOPN ( 2, FILTER ( 'Table', 'Table'[flg] = "P" ), 'Table'[Date], DESC )
VAR topone =
MAXX ( toptwotableflagP, 'Table'[Date] )
VAR topsecond =
MINX ( toptwotableflagP, 'Table'[Date] )
RETURN
SWITCH (
TRUE (),
'Table'[Date] = topone, "Latest",
'Table'[Date] = topsecond, "Previous",
FORMAT ( 'Table'[Date], "dd/mm/yyyy hh:mm" )
)
 
 
 
 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Hi,

 

This solution works when the dates are unique but in my dataset there are duplicate dates (around 100 rows for each date) and when I try to implement then its only showing me the Latest but not the previous. Is there a way to account for the duplicates. Image shows that when there are duplicate dates the calculations are off.

 

Image3.jpg

how does the desired outcome of the above look like?

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


I added duplicate values to the date column which then gave me just the 'latest' value on not the previous. If you take a look at the Needed Column CC then you can see when the duplicates are introduced then the 'previous' value goes away. So irrespective of the duplicates, I need a 'Latest' and 'Previous' tagged for all the latest dates and previous dates.

 

Image4.jpg

Ashish_Mathur
Super User
Super User

Hi,

These calculated column formulas work:

[Rank of row where flg=P] = CALCULATE(COUNTROWS(Data),FILTER(Data,Data[Date]>=EARLIER(Data[Date])&&Data[flg]="P"))
Column = if(and(Data[Rank of row where flg=P]=1,Data[flg]="P"),"Latest",if(and(Data[flg]="P",Data[Rank of row where flg=P]=2),"Previous",Data[Date]&""))

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

Check it Out!

Mark your calendars and join us on Thursday, September 29 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors