Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Rollup the date based on condition

Hello Everyone I would like to achieve the rollup for each Category -  with the max date where color is not equal to red Below is the sample data -  I am getting the wrong highlighted da...
DAX Syntax
rolling dates
  • AlexisOlson's avatar
    AlexisOlson
    5 years ago

    Try this:

     

    Lastest Date =
IF (
    HASONEVALUE ( Table1[Color] ),
    MAX ( Table1[Date] ),
    CALCULATE ( MAX ( Table1[Date] ), Table1[Color] <> "Red" )
)
AlexisOlson's avatar
AlexisOlson
Icon for Super User rankSuper User
5 years ago

Try this:

 

Lastest Date =
IF (
    HASONEVALUE ( Table1[Color] ),
    MAX ( Table1[Date] ),
    CALCULATE ( MAX ( Table1[Date] ), Table1[Color] <> "Red" )
)
  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Thanks a lot. This is so helpful.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    AlexisOlson - 

    Using the above measure you suggested is taking longer to process the data. 

    Is there a way to use any other formula instead? 
    I also learnt that the Hasonevalue takes longer time to process the query. 

    • AlexisOlson's avatar
      AlexisOlson
      Icon for Super User rankSuper User
      5 years ago

      See if this works any faster:

      Lastest Date =
IF (
    HASONEVALUE ( Table1[Color] ),
    MAX ( Table1[Date] ),
    CALCULATE ( MAX ( Table1[Date] ), KEEPFILTERS ( Table1[Color] <> "Red" ) )
)