Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
psmith-nhs-inc
Helper III
Helper III

DistinctCount help with DATEADD only works with contigous date selections

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

 

 

 

 

 

1 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

View solution in original post

7 REPLIES 7
kcantor
Community Champion
Community Champion

@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.

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




No Joy.

 

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

 

Gives me the same "contiguous date selections" error.

 

 

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.Smiley Happy

 

Regards

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
Employee
Employee

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?Smiley Happy

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

 

Regards

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
Helper III
Helper III

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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.