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 Previo...
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.

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