DAX number of days between dates

[robarivas]

Need a little DAX help. I have a transactions table (the many side) with transaction dates. And I have a customer table (the one side) with just one date per customer. I can probably do a calculated column to find the number of days between transaction date and "customer date". But I can't figure it out using a measure. Here is my attempt below:

CALCULATE

     (SUMX(TransactionsTable,

          DATEDIFF(

               RELATED(CustomerTable[Cust_Date]),TransactionsTable[Transaction_Date],DAY)),

     Transactions[LateType]="Late")

I keep getting an error about start date cannot be greater than end date. I may have situtations though where that exists or where there is no customer date or no customer found. I believe I would want to exclude such cases.

[Eric_Zhang]

---

@robarivas wrote:

Need a little DAX help. I have a transactions table (the many side) with transaction dates. And I have a customer table (the one side) with just one date per customer. I can probably do a calculated column to find the number of days between transaction date and "customer date". But I can't figure it out using a measure. Here is my attempt below:

CALCULATE

     (SUMX(TransactionsTable,

          DATEDIFF(

               RELATED(CustomerTable[Cust_Date]),TransactionsTable[Transaction_Date],DAY)),

     Transactions[LateType]="Late")

I keep getting an error about start date cannot be greater than end date. I may have situtations though where that exists or where there is no customer date or no customer found. I believe I would want to exclude such cases.

---

Could you post any sample data in your case? Even better you can share a pbix file.

[dkay84_PowerBI]

You just need to wrap your measure in a SWITCH or IF argument, with the conditions being if start date < end date, do X, otherwise if start date > end date, do Y.

X is what you already have, Y would be the same thing but with the order of the dates in the DATEDIFF reversed.  Depending on your use case, you may need to multiply that by -1 to reflect the DATEDIFF is not the same as in the usual case.