Change dax measure to take into account leap years.

[JPGV]

I currently have the following DAX in place (which works) to calculate sales for lost customers (based on a post by Enterprise DNA). In short, I wanted to make sure a customer had purchased something in the last 3 years, but did not purchase anything in the last 12 months. Right now I've simply used the # of days to make this calculation (1095 and 365).

Lost Customers Sales =
VAR Customerpurchase3y = CALCULATETABLE( VALUES( 'fact FactInvoiceLines'[SellToCustomerID] ),
filter( all( 'dim DimDate' ),
'dim DimDate'[DATE] > MIN ('dim DimDate'[DATE] ) - 1095 &&
'dim DimDate'[DATE] < MIN('dim DimDate'[DATE] ) - 365 ) )
VAR Customerpurchase1y = CALCULATETABLE( VALUES( 'fact FactInvoiceLines'[SellToCustomerID] ),
FILTER( ALL( 'dim DimDate' ),
'dim DimDate'[DATE] > MIN( 'dim DimDate'[DATE] ) - 365 &&
'dim DimDate'[DATE] < MIN('dim DimDate'[DATE])))
RETURN
CALCULATE(
CALCULATE( [Sales ACT],
DATESBETWEEN( 'dim DimDate'[DATE], MIN( 'dim DimDate'[DATE] ) - 1095, MIN( 'dim DimDate'[DATE] ) - 365 ) ) ,
EXCEPT( Customerspurchase, PriorCustomers ) ) * -1

I'm now trying to make this measure slightly more accurate by changing the -1095 days and -365 to -3 and -1 year, so it will also take into account leap years. However, I can't seem to get it to work. I've tried replacing the 'MIN ('dim DimDate'[DATE] ) - 1095' part of the formula by CALCULATE(MIN ('dim DimDate'[DATE]), DATEADD('dim DimDate'[DATE], - 3,YEAR ) but this is not giving me any results. Also I've tried CALCULATE(MIN ('dim DimDate'[DATE]), YEAR('dim DimDate'[DATE]) -3) which did not work. With the DATEADD the dax would look like this:

Lost Customers Sales test =
VAR Customerpurchase3y = CALCULATETABLE( VALUES( 'fact FactInvoiceLines'[SellToCustomerID] ),
filter( ALL( 'dim DimDate' ),
'dim DimDate'[DATE] > CALCULATE(MIN ('dim DimDate'[DATE]), DATEADD('dim DimDate'[DATE], - 3,YEAR ) ) &&
'dim DimDate'[DATE] < CALCULATE(MIN( 'dim DimDate'[DATE]), DATEADD('dim DimDate'[DATE], - 1, YEAR ) ) ) )
VAR Customerpurchase1y = CALCULATETABLE( VALUES( 'fact FactInvoiceLines'[SellToCustomerID] ),
FILTER( ALL( 'dim DimDate' ),
'dim DimDate'[DATE] > CALCULATE(MIN( 'dim DimDate'[DATE]), DATEADD('dim DimDate'[DATE], - 1, YEAR ) ) &&
'dim DimDate'[DATE] < MIN('dim DimDate'[DATE])))
RETURN
CALCULATE(
CALCULATE( [Sales ACT],
DATESBETWEEN( 'dim DimDate'[DATE], CALCULATE(MIN ('dim DimDate'[DATE]), DATEADD('dim DimDate'[DATE], - 3, YEAR)), CALCULATE(MIN( 'dim DimDate'[DATE]), DATEADD('dim DimDate'[DATE], - 1, YEAR ) ) ) ,
EXCEPT( Customerpurchase3y, Customerpurchase1y) ) * -1)

Does anyone have any advice on how to tackle this? My model has a Date table, Customer dimension and Invoices fact table. All connected with ID's. Due to sensitivity it's difficult to add sample data, but I'm hoping someone can point me in the right direction with this information. Thank you in advance.

[TeigeGao]

Hi @JPGV ,

The function DATEADD() is a Time-intelligence function, generally, a Time-intelligence function requires contiguous selection of time, as a result, we need to create a contiguous date table and create relationship between this table and the original table. If it is a non-contiguous table, it will return null.

Best Regards,

Teige

[JPGV]

Hi @TeigeGao, 

Thanks for your reply, I'm aware of this and I have a contiguous date table set-up (marked as date table) in my model. I've been able to use the DATEADD() function for other elements of my reports, but it does not seem to work in the measure. I showed it as an example of what I've tried so far, but I'm open for any other suggestions.