Forum Discussion

mterry's avatar
mterry
Helper V
6 years ago
Solved

Question about groupings

I'm running into an issue with grouping data - I'd like to take the chronological order into account and not sure if it's possible. For instance, with the below data, when I group by amount and use m...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi mterry ,

    You can create a calculated column as below to achieve it:

    First Date = 
VAR predate =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Date] < EARLIER ( 'Table'[Date] ) )
    )
VAR predAmount =
    CALCULATE (
        MAX ( 'Table'[Amount] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Date] = predate )
    )
RETURN
    IF ( predAmount <> [Amount], 'Table'[Date] )

    It also can be achieved it by creating a measure, you can get all details in this sample pbix file.

    Best Regards

    Rena

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi mterry ,

You can create a calculated column as below to achieve it:

First Date = 
VAR predate =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Date] < EARLIER ( 'Table'[Date] ) )
    )
VAR predAmount =
    CALCULATE (
        MAX ( 'Table'[Amount] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Date] = predate )
    )
RETURN
    IF ( predAmount <> [Amount], 'Table'[Date] )

It also can be achieved it by creating a measure, you can get all details in this sample pbix file.

Best Regards

Rena

mterry's avatar
mterry
Helper V
6 years ago

Thank you both, that helped