Forum Discussion

sridharpolina's avatar
sridharpolina
Icon for Helper I rankHelper I
5 years ago
Solved

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 ...
Need Help
Show and Tell
Tips and Tricks
  • v-angzheng-msft's avatar
    v-angzheng-msft
    5 years ago

    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.

v-angzheng-msft's avatar
v-angzheng-msft
Icon for Community Support rankCommunity Support
5 years ago

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.