Dax get the value of the oldest row of a group

[Anonymous]

Hi everyone.

I have a table with debits, credits and other columns... some of those are the column with accounting accounts, initial balance and date of the initial balance.

The accounting accounts repeat in several raws, repeating the initial balance, so I need a measure which give me the first initial balance (ordered by the date of initial balance) of each accounting account.

For example:

Account   date                 initial balance

2               21/01/2020     $2

2                22/01/2020     $20

3               01/01/2019      $0

3                02/01/2019    $10

Result:

Account   date                 initial balance

2               21/01/2020     $2

3               01/01/2019      $0

Please if someone have an idea, I'll be so grateful.

[Greg_Deckler · Accepted solution]

@eduardosilvin - This looks like Lookup Min/Max - https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/m-p/985814#M434

If applicable, you can use MIN or "first" to get your Date in your visualization. For the initial balance:

Initial Balance Measure = 
  VAR __Account = MAX([Account])
  VAR __MinDate = MINX(FILTER('Table',[Account] = __Account),[date])
RETURN
  MINX(FILTER('Table',[Account] = __Account && [date] = __date),[initial balance])

[Anonymous]

@Greg_Deckler works perfectly, thank you sooooo much!!!!

[Greg_Deckler]

@Anonymous - Sorry that was supposed to be:

Initial Balance Measure = 
  VAR __Account = MAX([Account])
  VAR __MinDate = MINX(FILTER('Table',[Account] = __Account),[date])
RETURN
  MINX(FILTER('Table',[Account] = __Account && [date] = __MinDate),[initial balance])

[Anonymous]

@Greg_Deckler thank you, I have one question, at the end you wrote "_date", but im not sure what date should be there

[amitchandak]

@Anonymous ,

Create a measure like this

firstnonblankvalue(Table[date],sum(Table[initial balance]))

And use with Account and min(Table[date]) or First Table[date] in a visual