Rolling 12M Average within Visual Studio not working

[sbarnhill75]

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.

[amitchandak · Accepted solution]

@sbarnhill75 , Make sure calendar_production is marked as date table and has a single directional join with facts

Try like

CALCULATE(
AVERAGEX (
VALUES ( 'Calendar_Production'[Month]),
[PDA Yield Avg]
),
DATESINPERIOD ( 'Calendar_Production'[Calendar_Date_info], MAX ( 'Calendar_Production'[Calendar_Date_info] ), - NumOfMonths, MONTH )
)

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.