cancel
Showing results for
Did you mean:
Helper III

## Change code to calculate incremental production per year

Hello I´m using the following code to calculate de incremental production per year but I´m having a problem when calculating the incremental production in the last year, I got 0 because the LastPeriodProduction and the FirstPeriodProduction is the same, then: LastPeriodProduction-FirstPeriodProduction=0 ... When there is only one date in a ayer I need to substract the previous year last production from the actual production like this:

Year 3 A:  800 (Jan-03) - 750 (mar-02) = 50

 TIME WELL WOPT Incremental prodUction jan-01 A 100 Year 1 A 200 feb-01 A 200 Year 2 A 250 march-01 A 300 Year 3 A 50 jan-02 A 500 feb-02 A 600 Year 1 B 200 march-02 A 750 Year 2 B 150 jan-03 A 800 Year 3 B 50 jan-01 B 200 feb-01 B 300 march-01 B 400 jan-02 B 800 feb-02 B 900 march-02 B 950 jan-03 B 1000

This is the code I am using:

Dif WOPT = VAR WELL = 'POZOS'[Well] VAR YEAR1 = YEAR ( 'POZOS'[TIME] ) VAR FirstPeriodInYear =
CALCULATE (
MIN ( 'POZOS'[TIME] );
FILTER (
ALL ( 'POZOS' );
YEAR ( 'POZOS'[TIME]) = YEAR1
&& 'POZOS'[Well] = WELL
)
) VAR LastPeriodInYear =
CALCULATE (
MAX ( 'POZOS'[TIME] );
FILTER (
ALL ( 'POZOS' );
YEAR ( 'POZOS'[TIME]) = YEAR1
&& 'POZOS'[Well] = WELL
)
) VAR FirstPeriodProduction =
CALCULATE (
SUM ( 'POZOS'[WOPT]);
FILTER (
ALL ( 'POZOS' );
'POZOS'[TIME] = FirstPeriodInYear
&& 'POZOS'[Well] = WELL
)
) VAR LastPeriodProduction =
CALCULATE (
SUM ( 'POZOS'[WOPT] );
FILTER (
ALL ( 'POZOS' );
'POZOS' [TIME] = LastPeriodInYear
&& 'POZOS'[Well] = WELL
)
) RETURN
IF (
'POZOS'[TIME] = LastPeriodInYear;
LastPeriodProduction - FirstPeriodProduction;
BLANK ()
)

5 REPLIES 5
Helper III

Hello @Anonymous  can you help me with this?

Anonymous
Not applicable
Hi there.

This looks to me like a job for Power Query, not DAX. Of course, you could do it in DAX but it's not quite the right tool.

Can't you do it in PQ?

Best
Darek
Helper III

Yes I can do it in power query but I don´t know how

Helper III

Hello @Nolock  can you help me?

Memorable Member

I'm very sorry, I haven't been writing DAX for long time and I'm not good at it anymore.

But if you have another PowerQuery question in the future, let me know 😉

Announcements