cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
OscarSuarez10
Helper III
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 

 

TIMEWELLWOPT Incremental prodUction 
jan-01A100 Year 1 A200
feb-01A200 Year 2 A250
march-01A300 Year 3 A50
jan-02A500   
feb-02A600 Year 1 B200
march-02A750 Year 2 B150
jan-03A800 Year 3 B50
jan-01B200   
feb-01B300   
march-01B400   
jan-02B800   
feb-02B900   
march-02B950   
jan-03B1000   

 

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
OscarSuarez10
Helper III
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

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

OscarSuarez10
Helper III
Helper III

Hello @Nolock  can you help me?

Hi @OscarSuarez10,

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 😉

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors