powerbidax

PowerBI DAX - Getting average Datediff in days


In PowerBI, I have the following tables :

  1. Date_List
Date
02/08/2015
03/08/2015
04/08/2015
05/08/2015
...
06/02/2025
  1. Main
Unit Status Start Date End Date
Unit 1 Finished 28/02/2016 12/04/2016
Unit 1 On-going 27/05/2017
Unit 2 On-going 05/01/2025
... ... ... ...
Unit 5 Finished 01/01/2025 02/02/2025

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
  1. STEP 2
  1. STEP 3
  1. STEP 4

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:

Date Avg_DateDiff_Days
02/08/2015 -300
03/08/2015 -280
04/08/2015 -260
...
06/02/2025 12

Note: Avg_DateDiff_Days values are completely false here.


Solution

  • 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]
        )