How can I create a custom column that averages a row if it is in a specific date range?

[mpompilio]

Let's say I have the below data: 

Date	Total
10/25/2021	14
11/2/2021	42
12/7/2021	31
1/16/2022	51
1/18/2022	57
4/21/2022	72
6/21/2022	43
7/4/2022	27

How can I create a custom column that will average the numbers in a specific date range? 
i.e. create a column with (sudo):

if [Date] between 10/01/2021 and 12/31/2021 then AVERAGE(Total) else if [Date] between 1/01/2022 and 7/31/2022 then AVERAGE(Total) 

Or something similar to that?

[ichavarria]

You can create a custom column using the following steps:

1. Click on "Add Column" in the "Modeling" tab of the Power BI desktop.
2. Click on "Custom Column" in the dropdown menu.
3. In the formula bar, enter the following formula:
   
   Avg Total =
   IF(
         AND(
                  [Date] >= DATE(2021, 10, 1),
                  [Date] <= DATE(2021, 12, 31)
          ),
         AVERAGE(Table1[Total]),
         IF(
                 AND(
                           [Date] >= DATE(2022, 1, 1),
                           [Date] <= DATE(2022, 7, 31)
                 ),
                 AVERAGE(Table1[Total]),
                 BLANK()
        )
   )
4. Replace "Table1" with the name of your table and "Total" with the name of your total column.
5. Press "Enter" to create the custom column.
   
   

This formula checks if the date falls within the range of 10/01/2021 and 12/31/2021, and returns the average of the total column for that date range. If the date falls within the range of 1/01/2022 and 7/31/2022, it returns the average of the total column for that date range. If the date falls outside of those date ranges, it returns a blank value.

Best regards, 

Isaac Chavarria 


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[mpompilio]

Thank you! This helps a bunch. But the issue that I am getting is that they are still just averaging all of them, despite specifying the date ranges. 

I think it is because it is looking for  [Date] >= DATE(2021, 10, 1), and since they are all greater than or equal to that, it is just looking at that and averaging every row. 

Any suggestions to stop that?