Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

DAX Calculation for Dates

Hi, I have a datasource that updates at a regular basis, 

 

is there a way to categorize them, for either "Current" or "Previous" then the rest will be NULL.

 

Current means Latest update

Previous means previous update

 

The rest will show null as we only want to show current update and previous update

 

Goal (Tableau Reference):

 

 

 

Goal:

 

LAST_UPDATE_DATEDAX calc
6/15/20201 2:14:34 AMnull
6/15/20201 9:42:25 AMPrevious
6/15/20201 6:34:25 PMCurrent 

 

Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous ,

     

    You can create the following measure

    Week = var _rank=RANKX(ALL('Table'),CALCULATE(MAX('Table'[Last Update Date])),,DESC,Dense)
return IF(_rank=1,"Current Week",IF(_rank=2,"Previous Week","Null"))

    If you only want to keep three rows, write a metric to keep the first three rows.

    Measure = var _rank=RANKX(ALL('Table'),CALCULATE(MAX('Table'[Last Update Date])),,DESC,Dense)
return IF(_rank<=3,1)

     

     

    Best Regards,

    Stephen Tao

     

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

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    I found a solution that might help you.

    I changed the datatype for the date column to date and I added a custom column with an if statement:

     

    IF('Table'[Date]=max('Table'[Date]),"CURRENT","OLDER")

     

     

    I get the following results in a table:

     

     

    Hope it helps.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi, thanks for your suggestion.

       

      However, i only want to have 1 older and 1 current, the history should be null.

       

      It's gonna update weekly, this is an example

       

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous ,

     

    You can create the following measure

    Week = var _rank=RANKX(ALL('Table'),CALCULATE(MAX('Table'[Last Update Date])),,DESC,Dense)
return IF(_rank=1,"Current Week",IF(_rank=2,"Previous Week","Null"))

    If you only want to keep three rows, write a metric to keep the first three rows.

    Measure = var _rank=RANKX(ALL('Table'),CALCULATE(MAX('Table'[Last Update Date])),,DESC,Dense)
return IF(_rank<=3,1)

     

     

    Best Regards,

    Stephen Tao

     

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