DistinctCount help with DATEADD only works with contigous date selections

[psmith-nhs-inc]

Sales table with customers, orders, items.  Standard DATE TABLE.  A year slicer with current year chosen.  (2017) Other slicers for product type, brand, etc.

CustomerCount = DISTINCTCOUNT(Sales[customer_id])

This gives me the number of customers who have purchased items of the selected brand or product type, for the full selected year, (with this year selected, only two months worth...)

What I want now is the number of customers who have purchased items of the selected brand or product type, for the full year previous. 

CustomerCountPY = CALCULATE(DISTINCTCOUNT(Sales[customer_id]), DATEADD(('Date'[Date]), -1, YEAR))

Doesn't work because of non contiguous dates.

So how do I get this measure to work?

Thanx

Phil

[kcantor]

@psmith-nhs-inc

You need to use your previously correct measure in your calculate instead of recreating the calculation.

CustomerCount = DISTINCTCOUNT(Sales[customer_id])

CustomerCountPY= CALCULATE([CustomerCount], DATEADD('Date'[Date], -1, year))

Also, it appears that you have extra parenthesis in your DATEADD portion of your measure.

Hope this helps.

[psmith-nhs-inc]

No Joy.

CustomerCountPY= CALCULATE([CustomerCount], DATEADD('Date'[Date], -1, year))

Gives me the same "contiguous date selections" error.

[v-ljerr-msft]

Hi @psmith-nhs-inc,

Could you post your table structures with some sample data which can reproduce the issue? It's better to share a sample pbix file. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.

Regards

[psmith-nhs-inc · Accepted solution]

I started cutting it down and editing it for privacy.  As I removed a table, completely unrelated to anything I am doing here, suddenly it started working.  I am not sure how the relationship was interfering with this,but after changing the relationship, (Both to Single) completely unrelated to this, it worked,  Now I have to make sure that nothing else broke with this relationship change, but I got the problem figured out.

Thanx everyone.

Phil

[v-ljerr-msft]

Hi @psmith-nhs-inc,

---

CustomerCountPY = CALCULATE(DISTINCTCOUNT(Sales[customer_id]), DATEADD(('Date'[Date]), -1, YEAR))

Doesn't work because of non contiguous dates.

So how do I get this measure to work?

---

Could you try the formula below to see if it works?

CustomerCountPY =
VAR currentSelectYear =
    YEAR ( MAX ( 'Date'[Date] ) )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( Sales[customer_id] ),
        FILTER ( 'Date', YEAR ( 'Date'[Date] ) = currentSelectYear - 1 )
    )

Regards

[psmith-nhs-inc]

Well, it doesn't complain with an error, but it also does not return anything.

I have a matrix where I am displaying CustomerCount and CustomerCountPY by Brand.

I get accurate numbers for CustomerCount, blank space for CustomerCountPY.

Thanx

Phil

[psmith-nhs-inc]

Another attempt to fix it created another issue I do not understand.

LastDate = LOOKUPVALUE('Date'[Date], 'Date'[Date], CALCULATE(MAX('Sales'[invoice_date]), ALL('Sales')))

StartDatePY = YEAR(DATEADD([LastDate], -1, YEAR))

This measure fails with an error I do not understand:

CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

Where is the True/False expression?

Phil