Forum Discussion

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

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

I created a version of the column with the help of the community where the calaculation of the latest and previous date are based on another column but I need to incorporate an additional column to t...
Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi sridharpolina ,

    Please change the measure to a column.

    Column = 
var _rankx=RANKX(filter(ALLSELECTED('Table'),'Table'[State]=EARLIER('Table'[State])),'Table'[AsOfDate],,DESC,Dense)
RETURN 
IF(_rankx=1,"Latest Runtime",IF(_rankx=2,"Previous Runtime",FORMAT(('Table'[AsOfDate]),"General Date")))
    _Rank_P = 
VAR _rank =
   RANKX ( FILTER  ( 'Table' , OR(('Table'[Flg] = "P"),Table[State]=EARLIER(Table[State]))), 'Table'[AsOfDate],, DESC, DENSE )
VAR _isP =
    IF ( 'Schedules'[Flg] = "P" , _rank, BLANK () ) 
RETURN
    _isP

     

    Best Regards

    Community Support Team _ Polly

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

sridharpolina's avatar
sridharpolina
Icon for Helper I rankHelper I
4 years ago

I was calculating the Rank_P column but not getting the desired output as it does not incorporate the State column. I need the latest datetime for any state tagged as Latest and the second datetime as previous. So irrespective of the datetimes I need the state to determine if the datetime is tagged as latest or not. The data below is the table I have as I was calaculating the Rank_P using DAX. 

 

 

AsOfDateStateFlgCurrent Date-RollupDesired Date-Rollup
6/13/2022 7:00DPLatest RuntimeLatest Runtime
6/13/2022 7:00LPLatest RuntimeLatest Runtime
6/13/2022 7:00MPLatest RuntimeLatest Runtime
6/13/2022 7:00MRPLatest RuntimeLatest Runtime
6/13/2022 7:00PetChemPLatest RuntimeLatest Runtime
6/13/2022 7:00STPLatest RuntimeLatest Runtime
6/13/2022 7:00TRPLatest RuntimeLatest Runtime
6/13/2022 6:00WNPPrevious RuntimeLatest Runtime
6/10/2022 7:00DP6/10/2022 7:00Previous Runtime
6/10/2022 7:00LP6/10/2022 7:00Previous Runtime
6/10/2022 7:00MP6/10/2022 7:00Previous Runtime
6/10/2022 7:00MRP6/10/2022 7:00Previous Runtime
6/10/2022 7:00PetChemP6/10/2022 7:00Previous Runtime
6/10/2022 7:00STP6/10/2022 7:00Previous Runtime
6/10/2022 7:00TRP6/10/2022 7:00Previous Runtime
6/10/2022 6:00WNP6/10/2022 6:00Previous Runtime
6/9/2022 7:00DP6/9/2022 7:006/9/2022 7:00
6/9/2022 7:00LP6/9/2022 7:006/9/2022 7:00
6/9/2022 7:00MP6/9/2022 7:006/9/2022 7:00
6/9/2022 7:00MRP6/9/2022 7:006/9/2022 7:00
6/9/2022 7:00PetChemP6/9/2022 7:006/9/2022 7:00
6/9/2022 7:00STP6/9/2022 7:006/9/2022 7:00
6/9/2022 7:00TRP6/9/2022 7:006/9/2022 7:00
6/9/2022 6:00WNP6/9/2022 6:006/9/2022 6:00
Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi sridharpolina ,

Please have a try.

Create a measure.

 

Measure =
VAR _rankx =
    RANKX (
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[State] = SELECTEDVALUE ( 'Table'[State] )
        ),
        CALCULATE ( MAX ( 'Table'[AsOfDate] ) ),
        ,
        DESC,
        DENSE
    )
RETURN
    IF (
        _rankx = 1,
        "Latest Runtime",
        IF (
            _rankx = 2,
            "Previous Runtime",
            FORMAT ( MAX ( 'Table'[AsOfDate] ), "General Date" )
        )
    )

 

 

Best Regards

Community Support Team _ Polly

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

  • sridharpolina's avatar
    sridharpolina
    Icon for Helper I rankHelper I
    4 years ago

    Thanks for all your help but I need a calculated column as an output since I need to use that column in a slicer. I tried to rank using the DAX code below but its not giving me the right output. Can you modify that query to reflect the state so I could get the desired output as a calculated column

     

     

     

     

    _Rank_P = 
VAR _rank =
   RANKX ( FILTER ( ALL ( 'Table' ), OR(('Table'[Flg] = "P"),Table[State]=SELECTEDVALUE(Table[State]))), 'Table'[AsOfDate],, DESC, DENSE )
VAR _isP =
    IF ( 'Schedules'[Flg] = "P" , _rank, BLANK () ) 
RETURN
    _isP

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi sridharpolina ,

      Please change the measure to a column.

      Column = 
var _rankx=RANKX(filter(ALLSELECTED('Table'),'Table'[State]=EARLIER('Table'[State])),'Table'[AsOfDate],,DESC,Dense)
RETURN 
IF(_rankx=1,"Latest Runtime",IF(_rankx=2,"Previous Runtime",FORMAT(('Table'[AsOfDate]),"General Date")))
      _Rank_P = 
VAR _rank =
   RANKX ( FILTER  ( 'Table' , OR(('Table'[Flg] = "P"),Table[State]=EARLIER(Table[State]))), 'Table'[AsOfDate],, DESC, DENSE )
VAR _isP =
    IF ( 'Schedules'[Flg] = "P" , _rank, BLANK () ) 
RETURN
    _isP

       

      Best Regards

      Community Support Team _ Polly

       

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly