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

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi sridharpolina ,

Please have a try.

Create a measure.

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

If I have misunderstood your meaning, please provide your desired output with more details and you sample pbix file without privacy information.

 

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