Need to substract production of a same product at different dates

[OscarSuarez10]

Hi, I need to substract the inital production and the final production of one product at different years,

For example for the product AK01 need to substract the production (WOPT) at the last date of 2019 - the production (WOPT) at the fist date of 2019, the I have to do the same for 2020, 2021 2022 ... The table has other products and I have to do the same thing for each product

[Stachu]

I don't really get what you're trying to achieve. Can you paste the same table with column that would show the output (as copiable table so I can put it in PowerBI and try calculating myself)

[OscarSuarez10]

Hello This the output that I wan to achieve:

[Stachu]

Isn't that's just a simple sum? I cannot type all the data manually, can you post it in theformat as below (just copy from Excel) into the reply window

Column1	Column2
A	2
B	2.1
C	3

[OscarSuarez10]

No, I need to calculate de prduction of each object per year, as you can see in the Output columns (red)

TIME	OBJECT	PRODUCTION	OUTPUT
30/05/2019	OB1	3,38E+11	YEAR	OBJECT	DIFERENTIAL PRODUCTION PER YEAR
29/06/2019	OB1	3,39E+11	2019	OB1	-3,032E+11
29/07/2019	OB1	34100000000	2020	OB1	20000000000
28/08/2019	OB1	34300000000	2019	CB19	207788
27/09/2019	OB1	3,44E+11	2020	CB19	141234
27/10/2019	OB1	3,46E+11
26/11/2019	OB1	34800000000
26/12/2019	OB1	3,5E+11
25/01/2020	OB1	3,51E+11
24/02/2020	OB1	3,53E+11
25/03/2020	OB1	3,55E+11
24/04/2020	OB1	3,57E+11
24/05/2020	OB1	3,58E+11
23/06/2020	OB1	36000000000
23/07/2020	OB1	3,62E+11
22/08/2020	OB1	3,64E+11
21/09/2020	OB1	3,66E+11
21/10/2020	OB1	3,68E+11
20/11/2020	OB1	36900000000
20/12/2020	OB1	3,71E+11
31/03/2019	CB19	349107
30/04/2019	CB19	371902
30/05/2019	CB19	394911
29/06/2019	CB19	417173
29/07/2019	CB19	440726
28/08/2019	CB19	465046
27/09/2019	CB19	488512
27/10/2019	CB19	511962
26/11/2019	CB19	534353
26/12/2019	CB19	556895
25/01/2020	CB19	579036
24/02/2020	CB19	600047
25/03/2020	CB19	621087
24/04/2020	CB19	641970
24/05/2020	CB19	662074
23/06/2020	CB19	682201
23/07/2020	CB19	701256
22/08/2020	CB19	720270

[v-juanli-msft · Accepted solution]

Hi @OscarSuarez10 

If your requirement is:

for 2020, you need the difference:

[data_of_first_date_of_2020]- [data_of_last_date_of_2019]

You could create a column

year = YEAR([TIME])

Create measures

data_of_first_date_per_year =
VAR min_date =
    CALCULATE (
        MIN ( Sheet4[TIME] ),
        ALLEXCEPT ( Sheet4, Sheet4[OBJECT ], Sheet4[year] )
    )
RETURN
    CALCULATE (
        SUM ( Sheet4[PRODUCTION] ),
        FILTER (
            ALLEXCEPT ( Sheet4, Sheet4[OBJECT ], Sheet4[year] ),
            [TIME] = min_date
        )
    )


data_of_last_date_per_year =
VAR max_date =
    CALCULATE (
        MAX ( Sheet4[TIME] ),
        ALLEXCEPT ( Sheet4, Sheet4[OBJECT ], Sheet4[year] )
    )
RETURN
    CALCULATE (
        SUM ( Sheet4[PRODUCTION] ),
        FILTER (
            ALLEXCEPT ( Sheet4, Sheet4[OBJECT ], Sheet4[year] ),
            [TIME] = max_date
        )
    )

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Stachu]

this would work as a calculated column, you can then sum it with time aggregate - is it fine or do you need a measure?

Column =
VAR __Object = 'Table'[OBJECT ]
VAR __Year = YEAR ( 'Table'[IME] )
VAR __FirstPeriodInYear =
    CALCULATE (
        MIN ( 'Table'[IME] ),
        FILTER (
            ALL ( 'Table' ),
            YEAR ( 'Table'[IME] ) = __Year
                && 'Table'[OBJECT ] = __Object
        )
    )
VAR __LastPeriodInYear =
    CALCULATE (
        MAX ( 'Table'[IME] ),
        FILTER (
            ALL ( 'Table' ),
            YEAR ( 'Table'[IME] ) = __Year
                && 'Table'[OBJECT ] = __Object
        )
    )
VAR __FirstPeriodProduction =
    CALCULATE (
        SUM ( 'Table'[PRODUCTION] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[IME] = __FirstPeriodInYear
                && 'Table'[OBJECT ] = __Object
        )
    )
VAR __LastPeriodProduction =
    CALCULATE (
        SUM ( 'Table'[PRODUCTION] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[IME] = __LastPeriodInYear
                && 'Table'[OBJECT ] = __Object
        )
    )
RETURN
    IF (
        'Table'[IME] = __LastPeriodInYear,
        __LastPeriodProduction - __FirstPeriodProduction,
        BLANK ()
    )