Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
If your requirement is:
for 2020, you need the difference:
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.
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)
Hello This the output that I wan to achieve:
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 |
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 |
If your requirement is:
for 2020, you need the difference:
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.
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 () )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.