Forum Discussion
Helper ICreating 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.
| 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 |
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.
6 Replies
- v-angzheng-msft
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:
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. - Ashish_Mathur
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.
- Jihwan_KimNeeded 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] )RETURNSWITCH (TRUE (),'Table'[Date] = topone, "Latest",'Table'[Date] = topsecond, "Previous",FORMAT ( 'Table'[Date], "dd/mm/yyyy hh:mm" ))
sridharpolinaHi,
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?
