Rolling 12M Average within Visual Studio not working

sbarnhill75 — Tue, 13 Sep 2022 16:47:20 GMT

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.

Re: Rolling 12M Average within Visual Studio not working

amitchandak — Wed, 14 Sep 2022 03:56:01 GMT

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-bd52912a5bd4
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.

topic Re: Rolling 12M Average within Visual Studio not working in DAX Commands and Tips

Rolling 12M Average within Visual Studio not working

Re: Rolling 12M Average within Visual Studio not working