In PowerBI, I have the following tables :

1. Date_List

In PowerBI, I have the following tables :

1. Date_List

2. Main

I would like to construct a calculated column inside Date_List table that calculates the average DATEDIFF for every row of Date_List[Date] and between all rows of Main[Start Date], with some conditional checks.

This means that we want to evaluate the days difference between:

1. STEP 1

• All Start Dates of Main (28/02/2016, 27/05/2017, etc),
• versus 02/08/2015,

2. STEP 2

• All Start Dates of Main (28/02/2016, 27/05/2017, etc)
• versus 03/08/2015,

3. STEP 3

• All Start Dates of Main (28/02/2016, 27/05/2017, etc)
• versus 04/08/2015,

4. STEP 4

• And so on...

N. STEP N : compute the average

Avg_DateDiff_Days = AVERAGE(
  IF(
    Main[Start Date] < Date_List[Date],
    IF(
      Main[End Date] > Date_List[Date],
      DATEDIFF(Main[Start Date], Date_List[Date], DAY),
      BLANK()
     ),
  BLANK()
)

Expected output

For instance, the output should look like this:

Note: Avg_DateDiff_Days values are completely false here.

• powerbi
• dax

1 Answer 1

Create the calculated column:

Avg_DateDiff_Days = 
VAR _CurrentDate = Date_List[Date]
RETURN
    AVERAGEX (
        ADDCOLUMNS (
            CALCULATETABLE (
                Main,
                Main[Start Date] < _CurrentDate,
                Main[End Date] > _CurrentDate
            ),
            "@Datediff", DATEDIFF ( [Start Date], _CurrentDate, DAY )
        ),
        [@Datediff]
    )