DAX calculating a rolling average with AVERAGE

Question

while I was setting up my visual page on PowerBI, I came across a weird issue to address with. I was trying to calculate an average of some values which came togheter with their dates (date) in tableA.

More precisely, tableA has a date field and a numeric feature (feature) and there might be more values for the same date. In addition, the data field points to another data field in a common calendar table (calendarTable). I would like to calculate an average of 'feature' (let's say, the daily average).

To achieve this, I've tried to calculate a new measure as stated here:

Average = CALCULATE(
                       AVERAGE('tableA'[feature]),
                       USERELATIONSHIP('tableA'[date], 'calendarTable'[date]),
                       GROUPBY(date, 'calendarTable'[date])
                       )

What I got is a 'cumulative' average instead of a daily average. In other terms, for each date the set of values to be averaged increases, including the previous values.

I've also tried to perform the calculation in SQL with success (in DAX there is no need to refer to tableB as I used a calculated column):

SELECT 
    CAST(a.Date AS Date) AS Dates, 
    AVG(DATEDIFF(MINUTE, b.Date, a.Date)) AS AVG_DURATION
FROM 
    tableB AS b
INNER JOIN 
    tableA AS a
ON 
    a.ID = b.ID
GROUP BY 
    CAST(a.Date AS Date)
ORDER BY 
    Dates ASC;

Does anyone have an idea on how to get in DAX the same result as in SQL? I've already tried to apply some filters on dates but with no luck.

Thanks.

Please add your sample data with expected output. And, your DAX requirement and SQL script is not referring the same issue. Average of a Numeric value and DATEDIFF is not same at all. — mkRabbani
'feature' is actually a difference between dates (datetime) in minutes. It's a calculated columns in tableA. As the output of the calculation is an integer, I thougth that AVERAGE would fit the case — FaboulousData

mkRabbani mkRabbani · Accepted Answer · 2020-08-24T04:46:50

Although there are some confusion in your question, this below instruction would help you achieving your requirement.

If I understand correct, you are looking for a daily AVEGARE of your column "Feature". You can do this with a new Custom Table, with GROUP BY functionality using DAX. Click on new table and use this below code and check you get your expected output or not-

group_by_date = 

GROUPBY (  
    your_table_name,
    your_table_name[date_column_name].[Date],
    "Average Feature", AVERAGEX(CURRENTGROUP(), your_table_name[feature])
)

Now, if you looking for DAX to calculate the Same (but redundant) result in each row, you can use this below code-

date_wise_average = 

VAR current_row_date = MIN(your_table_name[date_column_name].[Date])

RETURN
CALCULATE(
    AVERAGE(your_table_name[feature]),
    FILTER(
        ALL(your_table_name),
        your_table_name[date_column_name].[Date] = current_row_date 
    )
)

DAX calculating a rolling average with AVERAGE

1 Answers