Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
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 min date it groups all amounts together, regarldess of date, which I get is supposed to happen. What I'd like to have happen, is have it go through the data chronologically and incorporate the date when it groups - see the second table below. Is it possible to do this in Power Query?

 

DateAmount
1/1/20201000
2/1/20201500
3/1/20201500
4/1/20201000
5/1/20201500
6/1/20201500

 

Desired Result Actual Result
First DateAmount Min DateAmount
1/1/20201000 1/1/20201000
2/1/20201500 2/1/20201500
4/1/20201000   
5/1/20201500   
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Anonymous ,

    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

3 Replies