DAX Formula for Calculating Days Between Dates

Objective

To calculate the number of days between the dates in a row-by-row manner for a dataset containing Invoice Number and Date columns.

DAX Formula

Assuming your table is named Invoices, the following DAX formula can be utilized to create a new calculated column:

Days Between = 
IF (
    'Invoices'[Date] = BLANK() || 
    CALCULATE ( MAX ( 'Invoices'[Date] ), FILTER ( 'Invoices', 'Invoices'[Invoice Number] = EARLIER ('Invoices'[Invoice Number]) && 'Invoices'[Date] < EARLIER ('Invoices'[Date]))) = BLANK(),
    BLANK(),
    DATEDIFF (
        CALCULATE ( MAX ( 'Invoices'[Date] ), FILTER ( 'Invoices', 'Invoices'[Invoice Number] = EARLIER ('Invoices'[Invoice Number]) && 'Invoices'[Date] < EARLIER ('Invoices'[Date]))),
        'Invoices'[Date],
        DAY
    )
)

Explanation of the Formula

• IF Statement: The formula uses an IF statement to ensure that both the current row's date and the maximum previous date are not blank. If either is blank, it returns a BLANK result.

• CALCULATE Function: This function modifies the filter context. It retrieves the maximum date prior to the current row's date for the corresponding invoice number.

• FILTER Function: It assesses rows where the Invoice Number is the same and the Date is less than the current row’s date.

• DATEDIFF Function: This computes the difference in days between the two dates. The arguments specify that the unit of measurement is DAY.

• EARLIER Function: This enables access to the current row context of 'Invoices' to perform calculations against previous rows.

Practical Example

Dataset

Application

In the above table, applying the provided DAX formula as a calculated column would yield:

Results Interpretation

• For the first entry of each invoice, there is no previous date, thus it yields a BLANK.
• Subsequent entries contain the calculated number of days between the current invoice date and the latest preceding date of the same invoice number.

Conclusion

This DAX formula effectively computes the difference in days between dates in a row-by-row manner by leveraging conditional logic and context manipulation functions. This approach can be instrumental in analyses where understanding the time lag between transactions is critical. To further enhance your DAX skills, consider exploring the educational resources available on the Enterprise DNA Platform.