Power BI - DAX measure to calculate sales first 31 days - 'DATEADD' expects a contiguous selection

Question

Two tables. Sales_Fact & DateKey. Joined on date.

DateKey table dynamic based on Sales:

DateKey = CALENDAR(MIN(Sales_Fact[Date]),MAX(Sales_Fact[Date]))

Sales Table has the following structure.

Customer PurchaseDate  FirstTimePurchaseDate  Amount
A        2018/04/11       2018/02/26           500
b        2018/04/11       2018/03/01           300
c        2018/04/11       2018/04/11           400
A        2018/04/12       2018/02/26           100
b        2018/04/12       2018/03/01           200
c        2018/04/12       2018/04/11           400

I want to calculate total purchase amount per customer 31 days after they made their first purchase. The Sales table has continuous dates for the last 2 year, no 'purchase date' missing.

Following measure returns the 'DATEADD' expects a contiguous selection... error

First 31 days  = CALCULATE(SUM(Sales_Fact[Amount]),DATESBETWEEN(Sales_Fact[PurchaseDate],MIN(Sales_Fact[FirstTimePurchaseDate]),DATEADD(Sales_Fact[PurchaseDate],31,DAY)))

How should the DAX expression be written in order for this measure to work?

Thanks

RADO RADO · Accepted Answer · 2018-11-03T18:59:30

You can calculate the number of days since the first purchase for each transaction, and then sum up transactions where the number of days is <= 31:

=
SUMX (
    FILTER (
        Sales_Fact,
        Sales_Fact[PurchaseDate] - Sales_Fact[FirstTimePurchaseDate] <= 31
    ),
    Sales_Fact[Amount]
)

The same result can be achieved by creating a calculated column in fact Sales for the number of days since the first purchase:

Days Since First Purchase = Sales_Fact[PurchaseDate] - Sales_Fact[FirstTimePurchaseDate]

The measure "First 31 days" is then very simple:

= CALCULATE ( SUM(Sales_Fact[Amount]), Sales_Fact[Days Since First Purchase] <= 31)

Regarding the error you are getting - it's a different issue. DATEADD is a time intelligence function. Such functions usually operate on calendar dimensions, not on fact tables (exactly because the dimension has continuous dates, while fact tables usually don't). In your case, things are complicated by having 2 dates instead of one, so for your current approach to work you need to have 2 calendar tables, not one (i.e, "Purchase Calendar", and "First Time Purchase Calendar"). Better yet, you can re-design your Sales fact table to have only one date ("Purchase Date"). The first purchase date then can be either flagged in a column, or detected in DAX as a min Date for a customer. In other words, if you want to use calendar table, I would suggest to redesign your data model.

Power BI - DAX measure to calculate sales first 31 days - 'DATEADD' expects a contiguous selection

1 Answers