Forum Discussion

vinodDrinkPak's avatar
vinodDrinkPak
Helper II
2 years ago

Rolling average

Hi Experts

 

below is matrix visual in power bi where i have used rows as customer and coloumns as month and dax

Average_Total_CEs =
IF(
    ISBLANK(AVERAGE('CE Per Pallet'[Total CEs])),
    0,
    AVERAGE('CE Per Pallet'[Total CEs])
)

 

Now the requirement was to calculate the rolling average for which i used Chat gpt for the measure

 

Rolling_Average_Total_CEs =
VAR SelectedCustomer = SELECTEDVALUE('CE Per Pallet'[Customer Name])
VAR CurrentMonth = SELECTEDVALUE('CE Per Pallet'[EOM])

RETURN
IF(
ISBLANK([Average_Total_CEs]),
0,
AVERAGEX(
FILTER(
ALL('CE Per Pallet'),
'CE Per Pallet'[Customer Name] = SelectedCustomer &&
'CE Per Pallet'[EOM] <= CurrentMonth
),
[Average_Total_CEs] 
)
)

 

but looks like the outcome is not right, so for example 0+69/2, should be 34.5?

 

1 Reply