Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 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 dates - (it should filter out the date where it is red)

Result Expected For highlighted date- 
A - 11/18/2021
B - 10/11/2021


Thanks in advance. Would really appreciate the help.

DAX Syntax
rolling dates
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    Try this:

     

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

4 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    4 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
      4 years ago

      Thanks a lot. This is so helpful.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 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
        Super User
        4 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" ) )
)