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

[sridharpolina]

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.

Date	As of Date	flg	Needed Column
6/24/2021 6:00	Latest	P	Latest
6/23/2021 19:00	Previous	A	6/23/2021 19:00
6/23/2021 16:00	6/23/2021 16:00	A	6/23/2021 16:00
6/23/2021 14:00	6/23/2021 14:00	A	6/23/2021 14:00
6/23/2021 7:00	6/23/2021 7:00	P	Previous
6/22/2021 19:00	6/22/2021 19:00	P	6/22/2021 19:00
6/22/2021 16:00	6/22/2021 16:00	A	6/22/2021 16:00
6/22/2021 7:00	6/22/2021 7:00	P	6/22/2021 7:00
6/21/2021 19:00	6/21/2021 19:00	P	6/21/2021 19:00
6/21/2021 14:00	6/21/2021 14:00	A	6/21/2021 14:00
6/21/2021 7:00	6/21/2021 7:00	P	6/21/2021 7:00
6/20/2021 19:00	6/20/2021 19:00	A	6/20/2021 19:00
6/19/2021 19:00	6/19/2021 19:00	A	6/19/2021 19:00
6/18/2021 16:00	6/18/2021 16:00	A	6/18/2021 16:00
6/18/2021 12:00	6/18/2021 12:00	A	6/18/2021 12:00
6/18/2021 11:00	6/18/2021 11:00	A	6/18/2021 11:00
6/18/2021 6:00	6/18/2021 6:00	P	6/18/2021 6:00
6/17/2021 19:00	6/17/2021 19:00	P	6/17/2021 19:00
6/17/2021 17:00	6/17/2021 17:00	A	6/17/2021 17:00
6/17/2021 9:00	6/17/2021 9:00	P	6/17/2021 9:00

[v-angzheng-msft · Accepted solution]

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:

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]

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" )

)

https://www.dropbox.com/s/7lddl4zla0kvb2a/srid.pbix?dl=0 

[sridharpolina]

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.

[Jihwan_Kim]

how does the desired outcome of the above look like?

[sridharpolina]

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.

[Ashish_Mathur]

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.