Returning a starting and ending value based on another column

[Nazer]

Hello,

I started using Power Pivot and Power Query for the first time two days ago to analyze a spreadsheet of transactions. Some of the transactions in the spreadsheet are categorized into groups, e.g. "Group 1', "Group 2' and so on. Here's some sample data to illustrate:

Index	Date	Statement Balance	Groupings
1	5/04/2019	100	Ungrouped
2	5/04/2019	$400	Ungrouped
3	7/04/2019	247	Group 1
4	8/04/2019	300	Group 1
5	9/04/2019	567	Group 1
6	10/04/2019	543	Group 1
7	11/04/2019	912	Group 1
8	12/04/2019	342	Group 1
9	12/04/2019	678	Group 1
10	14/04/2019	986	Group 1
11	15/04/2019	$200	Group 1
12	16/04/2019	100	Group 2

I need assistance in getting the opening and closing value that corresponds to the particular group, I did a fair amount of research but was unable to find a solution. In reference to the sample data above, when analyzing group 1, the value $400 should be returned for the opening balance and $200 for the closing balance. The value I need is in the row before 'Group 1' starts. This is just an example there are more columns such as debits, credits, and account name. There are also gaps between the groupings so the opening balance of group 2 isn't the closing balance of group 1, it would the value in the balance column in the row before group 2 starts. The result I want cannot be calculated by debits or credits, it is independent.

Here is how I want the results visualised:

I have already created DAX measures to display the start/end date of the transactions that correspond to these groups and the number of days in that range.

These measures are functional and filter correctly based on the 'Groupings' column in my pivot table visualisation.

Here are my tables and relationships, all dimension tables including a date dimension table are connected to the main fact table. The balance amount and grouping category is column 'StaBalanceAmount' and 'CategoryGroup' respectively.

Thank you for any assistance.

[Greg_Deckler]

@Nazer So, you should be able to do something like:

Beginning Balance =
  VAR BeginningDate = FIRSTDATE(fTransactions[Date])
RETURN
  MAXX(FILTER(fTranscations,[Date]=BeginningDate),[Statement Balance])

[Nazer]

@Greg_Deckler 

Thanks for your help.

It definitely helped me progress in the right direction. The issue is for opening balance it is returning the value that corresponds to the first row of Group 1 instead of the one before it, so from my example in the original post it would return $247 instead of $400.

For the closing balance, since there are sometimes multiple transactions on the same closing date it is returning the balance amount of the first transaction for the date, not the last one which is what I need. So for example:

It is returning $150 instead of the $600 closing amount I need. I will look into possibly referencing the index number, since the right transaction would correspond to the max index number of the date range that is filtered to the groupings.

[v-cgao-msft · Accepted solution]

Hi @Nazer,

I use this table for testing.

Please create two measures.

Opening Statement Blance = 
VAR _group = MAX('Table'[Groupings])
VAR _index = CALCULATE(MIN('Table'[Index])-1,'Table'[Groupings]=_group)
VAR _statement_balance = CALCULATE(SUM('Table'[Statement Balance]),FILTER(ALL('Table'),'Table'[Index]=_index))
RETURN
_statement_balance

Closing Statement Blance = 
VAR _group = MAX('Table'[Groupings])
VAR _date = CALCULATE(MAX('Table'[Date]),'Table'[Groupings]=_group)
VAR _index = CALCULATE(MIN('Table'[Index]),'Table'[Date]=_date)
VAR _statement_balance = CALCULATE(SUM('Table'[Statement Balance]),FILTER(ALL('Table'),'Table'[Index]=_index))
RETURN
_statement_balance

Attached the pbix file for reference.

Best Regards,
Community Support Team_Gao

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[Nazer]

Thank you, yes this solution works. Late reply and I used a slightly different way but I'm sure your version is better.