DAX function to calculate average based on ID and dates

Question

I'm trying to calculate a 3 month average based on date and a change flag. For example, I have columns:

Date - ID item - Description - Sales Amount - Change (shows 1 when the name has change)

It's possible to calculate average taking in consideration the change column and date, for example the product name was changed on may 2019 so I want to have the average 3 months after may and 3 months before may.

The image shows the table I'm using:

enter image description here

mkRabbani mkRabbani · Accepted Answer · 2020-10-22T06:18:28

If I understand your requirement correct, following this below steps will take you to your desired output-

Step-1 Go to Power Query Editor and create 3 custom column as below-

start date before = Date.AddMonths([date],-2)

end date after = Date.AddMonths([date],3)

Change data type as Date after creating both custom columns and get back to report by clicking Close & Apply button.

Step-2 Create these below 2 Measure in your table. I have considered the table name as your_table_name (adjust your self)

3_month_average_before = 

VAR current_row_chaneg = MIN(your_table_name[change])
VAR current_row_start_date_before = MIN(your_table_name[start date before])
VAR current_row_date = MIN(your_table_name[date])
VAR current_row_desc = MIN(your_table_name[desc.])

RETURN
IF(
    current_row_chaneg <> 1,
    BLANK(),
    CALCULATE(
        SUM(your_table_name[sales]),
        FILTER(
            ALL(your_table_name),
            your_table_name[date] >= current_row_start_date_before
            && your_table_name[date] <= current_row_date
            && your_table_name[desc.] <> current_row_desc //--considered different product name for previous 3 month average
        )
    )
)

3_month_average_after = 

VAR current_row_chaneg = MIN(your_table_name[change])
VAR current_row_end_date_after = MIN(your_table_name[end date after])
VAR current_row_date = MIN(your_table_name[date])
VAR current_row_desc = MIN(your_table_name[desc.])

RETURN
IF(
    current_row_chaneg <> 1,
    BLANK(),
    CALCULATE(
        SUM(your_table_name[sales]),
        FILTER(
            ALL(your_table_name),
            your_table_name[date] <= current_row_end_date_after
            && your_table_name[date] > current_row_date
            && your_table_name[desc.] = current_row_desc //--considered same product name for after 3 month average
        )
    )
)

I have applied logics based on my understanding. You can adjust logic if required in the same process. Here below is the sample output from your given sample data-

Code for Column

change = 

VAR current_id  = your_table_name[id item]
VAR current_desc  = your_table_name[desc.]
VAR current_date  = your_table_name[date]

VAR current_item_count = 
CALCULATE(
    COUNTROWS(your_table_name),
    FILTER(
        ALL(your_table_name),
        your_table_name[id item] = current_id
        && your_table_name[desc.] = current_desc
        && your_table_name[date] <=current_date
    )
)

VAR previous_item_count = 
CALCULATE(
    COUNTROWS(your_table_name),
    FILTER(
        ALL(your_table_name),
        your_table_name[id item] = current_id
        && your_table_name[date] <= current_date
        && your_table_name[desc.] <> current_desc
    )
)

RETURN IF(current_item_count = 1 && previous_item_count <> 0 , 1, 0)

DAX function to calculate average based on ID and dates

1 Answers

Code for Column