Forum Discussion

sbarnhill75's avatar
sbarnhill75
Frequent Visitor
4 years ago
Solved

Rolling 12M Average within Visual Studio not working

Hello! 

I need to get a rolling 12 month average for the PDA Yield.  The code needs to work in Visual Studio.  This first DAX here I am getting the closest results, but they are not what I expect.

PDA Yield Rolling 12M Avg:=

VAR NumOfMonths = 12

VAR LastSelectedDate =

    MAX ( Calendar_Production[Calendar_Date_info] )

VAR Period =

    DATESINPERIOD (

        Calendar_Production[Calendar_Date_info],

        LastSelectedDate,

        - NumOfMonths,

        MONTH

    )

VAR MaxDate =

    LASTDATE ( Period )

VAR MinDate =

    FIRSTDATE ( Period )

VAR ActualMonths = DATEDIFF(MinDate, MaxDate, MONTH)   

VAR OilChgBPH =

    CALCULATE (

        SUMX ( 'FV PDA Unit Report', 'FV PDA Unit Report'[Oil Charge BPH] ),

        ALL ( 'FV PDA Unit Report' ),

        DATESBETWEEN ( Calendar_Production[Calendar_Date_info], MinDate, MaxDate )

    )

        / NumOfMonths

VAR OilRecoveryProdFlow =

    CALCULATE (

       SUMX('FV PDA Unit Report', 'FV PDA Unit Report'[Prod Flow BPH FR-1]),

        ALL ( 'FV PDA Unit Report' ),

        DATESBETWEEN ( Calendar_Production[Calendar_Date_info], MinDate, MaxDate )

    )

        / NumOfMonths

RETURN

DIVIDE(OilRecoveryProdFlow, OilChgBPH) * 100 

 

This is an export of the visual to Excel.  Column E is the above formula but column H is what I expect to be getting.  

The PDA Yield column itself is a formula: DIVIDE([Oil Recovery Prod Flow Total],[Oil Charge BPH Total])* 100.

I have also tried this and the results are further off:

PDA Yield Rolling 12M Avg2:=

VAR NumOfMonths = 12

VAR LastCurrentDate =

    MAX ( 'Calendar_Production'[Calendar_Date_info] )

VAR Period =

    DATESINPERIOD ( 'Calendar_Production'[Calendar_Date_info], LastCurrentDate, - NumOfMonths, MONTH )

VAR Result =

        CALCULATE(

            AVERAGEX (

                VALUES ( 'Calendar_Production'[Month]),

                [PDA Yield Avg]

             ),

            Period

            )

RETURN

                Result 

 

I feel like it is something obvious, but I just don't see it.

1 Reply