DAX Formula to Calculate Days Between Dates

Objective

To calculate the number of days between dates associated with invoice numbers across multiple rows. This involves using a fact table containing the invoice numbers and a date column linked to a dimension calendar table.

Assumptions

1. You have a fact table named FactInvoices that includes:

   • InvoiceNumber (identifier for invoices)
   • Date (date of the invoice)

2. You have a date dimension table named DimCalendar with:

   • Date (all available dates for analysis)

DAX Formula

The following DAX formula calculates the number of days between the current row date and the date from the previous invoice based on the InvoiceNumber within your model:

DaysBetweenInvoices = 
VAR CurrentInvoiceDate = MAX(FactInvoices[Date])
VAR PreviousInvoiceDate = 
    CALCULATE(
        MAX(FactInvoices[Date]),
        FILTER(
            FactInvoices,
            FactInvoices[InvoiceNumber] = EARLIER(FactInvoices[InvoiceNumber]) &&
            FactInvoices[Date] < CurrentInvoiceDate
        )
    )
RETURN
    IF(ISBLANK(PreviousInvoiceDate), BLANK(), DATEDIFF(PreviousInvoiceDate, CurrentInvoiceDate, DAY))

Explanation of the Formula

1. Variables Declaration:

   • CurrentInvoiceDate: This variable captures the maximum date for the current context in the FactInvoices table.
   • PreviousInvoiceDate: This variable calculates the maximum date of the previous invoice for the same invoice number, filtering out dates that are equal to or greater than the current date.

2. Calculation Logic:

   • The FILTER function is used to ensure we are only considering rows that share the same invoice number and have dates less than the CurrentInvoiceDate.
   • The DATEDIFF function calculates the number of days between the PreviousInvoiceDate and CurrentInvoiceDate.

3. Return Value:

   • The IF function checks if the PreviousInvoiceDate is blank (i.e., there is no previous invoice date). In such cases, it returns BLANK(), avoiding errors in the calculation.

Practical Illustration

Scenario

Assume the following entries in your FactInvoices table:

Result

Using the DaysBetweenInvoices calculation, the results would be:

• For Invoice 001, the second entry calculates 14 days from the first entry.
• For Invoice 002, the second entry calculates 15 days from the first entry.

Conclusion

This DAX calculation effectively computes the difference in days between dates for each invoice in your dataset. Adjust your table and column names as necessary to fit the specific structure of your data model. For further learning on DAX, consider exploring courses offered by Enterprise DNA.