Solved: Metric to sum the first value within each group

mattpowerbiuser · ‎11-12-2022

Hello, we have a dataset of animal biomass inventory at the end of each month for several groups of cultivated animals. We need to create reports to show the proportion of starting inventory that was harvested during each period. Therefore, we need to have the "starting" inventory (and also would like to have the "ending" inventory). New groups may enter inventory during the middle of the year, and we need the "starting" inventory at the first date the group entered inventory. So If the group entered inventory in June 2021, we need the "starting inventory" of the year 2021 to be the value at the end of June. So I believe we need to sum the first nonblank value of each group within each date context.

Below is a sample dataset:

Date	Group	Biomass
12/31/20	A
1/31/21	A
2/28/21	A
3/31/21	A
4/30/21	A
5/31/21	A
6/30/21	A	100
7/31/21	A	90
8/31/21	A	80
9/30/21	A	70
10/31/21	A	60
11/30/21	A	50
12/31/21	A	40
12/31/20	B	200
1/31/21	B	180
2/28/21	B	160
3/31/21	B	140
4/30/21	B	120
5/31/21	B	100
6/30/21	B	80
7/31/21	B	60
8/31/21	B	40
9/30/21	B	20
10/31/21	B	0
11/30/21	B	0
12/31/21	B	0

The results should look as follows:

	2021
Group A
Starting Biomass	100
Ending Biomass	40
Group B
Starting Biomass	200
Ending Biomass	0
Total
Starting Biomass	300
Ending Biomass	40

Your assistance is greatly appreciated.

Bujor · ‎11-12-2022

Starting Biomass =

FIRSTNONBLANKVALUE( 'Table'[Date], SUM( 'Table'[Biomass] ) )

Ending Biomass =

LASTNONBLANKVALUE( 'Table'[Date], SUM( 'Table'[Biomass] ) )

If these measures are used, then the starting & ending og Group B are 180 and 0, respectively.

https://app.powerbi.com/groups/me/reports/6419f58e-3bc7-47a2-b9ea-159b59e22f40?ctid=38b58b0d-74b4-46...

View solution in original post

mattpowerbiuser · ‎04-08-2023

Unfortunately the above solutions don't take into account that each group has a different balance at the end of each period. The balance must be found for each group and then summed. It took me several months to figure out, but finally getting a copy of DAX Patterns as well as suggestions from ChatGPT, I was able to verify that the solution below works:

InitialB = 
VAR myDate =
IF(
    ISBLANK(
        CALCULATE(LASTNONBLANK(FactTable[Date],0),
        FILTER(
            ALL('Date'), 
            YEAR('Date'[Date]) = YEAR(MIN('Date'[Date]) - 1)
        )
        )
    ),
    FIRSTNONBLANK(FactTable[Date],0),
    CALCULATE(
        LASTNONBLANK(FactTable[Date], 0),
        FILTER(
            ALL('Date'),
            YEAR('Date'[Date]) = YEAR(MIN('Date'[Date])) - 1
        )
    )
)

VAR MaxBalanceDates = 
ADDCOLUMNS(
    SUMMARIZE(FactTable,FactTable[Group]),
    "BalDate",
   myDate
)

VAR MaxBalanceDatesWithLineage =
    TREATAS(MaxBalanceDates, FactTable[Group], 'Date'[Date])
VAR Result =
    CALCULATE(SUM(FactTable[B (kg)]), MaxBalanceDatesWithLineage)
RETURN
    Result

Note that the measure has a special feature where if there is no available balance at the end of the prior period, use the first available balance during the filter context. E.g. if animals are born during the middle of the year, their initial balance for the year is the first available balance.