Problem with PriorYear Counting measure

[psmith-nhs-inc]

I get three out of four.  How do I get the last one to work?

I have a Sales table, with line item sales detail.  I want to know:

1) How many unique customers purchased this quarter. (whichever quarter is selected.)

2) How many unique customers whose sales exceeded $1000 this quarter. (whichever quarter is selected.)

3) How many unique customers purchased this quarter last year. (whichever quarter is selected.)

4) How many unique customers whose sales exceeded $1000 this quarter last year. (whichever quarter is selected.)

 -------------------------

1) CustomerCount = DISTINCTCOUNT(Sales[customer_id])

2) CustomerCountWhoMetMinimums = CALCULATE(DISTINCTCOUNT(Sales[customer_id]),filter(Sales, [CustomerSalesOverMin1k]="Met"))

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

4) CustomerCountWhoMetMinimumsPY = CALCULATE([CustomerCount], DATEADD('Date'[Date], -1, year),filter(Sales, [CustomerSalesOverMin1k]="Met"))

They all seem to work fine except for #4, which returns nothing.

If you need this:

CustomerSalesOverMin1k = if(Sales[Sale]>=1000,"Met","Not Met")

Not sure why #4 should be different.  Any ideas how to make this thing fly?

Thanx

Phil

[mattbrice · Accepted solution]

Wow - that's an impressive model.  Lots of tables and links. 

I found the syntax error by using intellisense to complete the code.  For whatever reason Power BI Desktop didn't like the table Date unless you enclosed with single quotes 'Date'.  So did that for both and syntax error solved and functions seem to work fine.  (And as I type this it occurs to me that "Date" is a keyword and the name of a function which is probably reason for syntax error. Single quotes allowed parser to understand it is a table name and not a function call.)

CustomerCountWhoMetMinimumsPYTest = 
CALCULATE ( [CustomerCountWhoMetMinimums], SAMEPERIODLASTYEAR ( 'Date'[Date] ))

CustomerCountWhoMetMinimumsTest = CALCULATE( [CustomerCount],FILTER( VALUES( Sales[customer_id] ), [Sale] > 50 ) )

or 

CustomerCountWhoMetMinimums = CALCULATE(DISTINCTCOUNT(Sales[customer_id]),filter(ValueS(Sales[customer_id]), [CustomerSalesOverMin50]="Met"))

Also, FYI you have a page level filter on "Prebook_Year" reducing sales to only 2016 and 2017.  So you'll only get values for prior years if you select "CY" in you slicer.

 Good luck and hope this helps....

[mattbrice]

I first would add a measure  Total Sales = SUM ( Sales[Sale] ) for use later.  Then:

1) CustomerCount = DISTINCTCOUNT( Sales[customer_id] )

2) CustomerCountWhoMetMinimums = CALCULATE( [CustomerCount],FILTER( VALUES( Sales[customer_id] ), [Total Sales] > 1000 ) )

3) CustomerCountPY = CALCULATE([CustomerCount], SAMEPERIODLASTYEAR( Date[Date] ) ) ( Just syntax sugar for code readability)

4) CustomerCountWhoMetMinimumsPY = CALCULATE ( [CustomerCountWhoMetMinimums], SAMEPERIODLASTYEAR( Date[Date] ) )

I obviously have not tried any of these, but they should work.

[psmith-nhs-inc]

Thank you for your response.

Sale is already a sum of the line item detail.  .  Sorry. that was not clear in my post.

Sale = SUM(Sales[Ext_Price])

You version of #3 and #4 fails with:

"the syntax for [Date] is incorrect."

This does not make send ot me since this field Date[Date] is in the format mm/dd/yyyy and is clearly a Date data type according to BI.

[mattbrice]

Two things:

1) When writing function code, the syntax protocol is to preface column names with the table name like "Sales[customer_id]". But for measures you don't preface wth table name. Like "[Total Sales]" or I think "[Sales]" is what you had. Makes it a lot easier to read and for others to help.
2) Check for missing paren, commas etc in code. If Date[Date] is a date column from your Calendar table, it should work. Another tool to use to help with syntax issues is www.daxformatter.com Paste the function in there and hit "Format" and it will either beautify the code or point out where it thinks you have a syntax error.

[psmith-nhs-inc]

1) Noted and appreciated.

2) Passed the syntax check.   I agree that

CustomerCountWhoMetMinimumsPYTest =
CALCULATE ( [CustomerCountWhoMetMinimums], SAMEPERIODLASTYEAR ( Date[Date] ) )

SHOULD work, as Date[Date] is indeed a date in both content and data type.

Yet it does not.

[mattbrice]

If it is still claiming there is a syntax error, something is not quite right.  Only other ideas i can think of is to make sure your calendar table does inded have all contiguous dates for this year and last, that your relating via date column.   Also you can try changing out the SAMEPERIODLASTYEAR for the DATEADD like you had before.  But like i say the former is just syntax sugar for the latter.  

[psmith-nhs-inc]

Which get's me back to square one, which is why does #4 return blanks for everything?

[v-ljerr-msft]

Hi @psmith-nhs-inc,

Based on my understanding, both the formulas below should work in your scenario.

CustomerCountWhoMetMinimumsPY = CALCULATE ( [CustomerCountWhoMetMinimums], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )

CustomerCountWhoMetMinimumsPY = CALCULATE ( [CustomerCountWhoMetMinimums], DATEADD ( 'Date'[Date], -1, YEAR ) )

To further investigate on this issue, 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]

Took a bit to scrub it.  Lots of stuff is broken as a lot of tables were flat out removed, the but the key pieces are here.

https://dl.dropboxusercontent.com/u/9936354/SalesDEVfor%20forumpost.pbix

[mattbrice · Accepted solution]

Wow - that's an impressive model.  Lots of tables and links. 

I found the syntax error by using intellisense to complete the code.  For whatever reason Power BI Desktop didn't like the table Date unless you enclosed with single quotes 'Date'.  So did that for both and syntax error solved and functions seem to work fine.  (And as I type this it occurs to me that "Date" is a keyword and the name of a function which is probably reason for syntax error. Single quotes allowed parser to understand it is a table name and not a function call.)

CustomerCountWhoMetMinimumsPYTest = 
CALCULATE ( [CustomerCountWhoMetMinimums], SAMEPERIODLASTYEAR ( 'Date'[Date] ))

CustomerCountWhoMetMinimumsTest = CALCULATE( [CustomerCount],FILTER( VALUES( Sales[customer_id] ), [Sale] > 50 ) )

or 

CustomerCountWhoMetMinimums = CALCULATE(DISTINCTCOUNT(Sales[customer_id]),filter(ValueS(Sales[customer_id]), [CustomerSalesOverMin50]="Met"))

Also, FYI you have a page level filter on "Prebook_Year" reducing sales to only 2016 and 2017.  So you'll only get values for prior years if you select "CY" in you slicer.

 Good luck and hope this helps....

[psmith-nhs-inc]

Actually, that just brings me back to square one.  The measures are all now acceptable to Power BI, but I still get Zilch returned for

CustomerCountWhoMetMinimumsPY.

[mattbrice · Accepted solution]

hmmm....the model i have does return value for "CustomerCountWhoMetMinimumsPYTest" although i used my formula and not yours.  

CustomerCountWhoMetMinimumsPYTest = 
CALCULATE ( [CustomerCountWhoMetMinimums], SAMEPERIODLASTYEAR ( 'Date'[Date] ))

Here's your model back so you can look at it:  Sharefile Link

[psmith-nhs-inc]

That was my mistake.  Yes your DAX works perfectly now.  Thank you again!

[psmith-nhs-inc]

I tried it out.  I knew it had to be something stupid simple, but that exceeded my expectations!

Thank you very much.

Yes, there is a lot of tables, and that is cut down just for sales, then cut down again for posting.  My core BI handles sales, inventory, budgeting, licensing, and is an absolute monster.  Trying to nail down relationship issues in that thing is a nightmare.

I know about the filter.  That acutally makes things easier for me.  Some users can get a little too creative, then expect me to explain their results.

Thank you very very much.  It is truly appreciated.

Phil

[mattbrice]

At this point for me to help i would need a copy of the model with sample data.  So if you can post one I would look at it as I am curious to know what the issue is  myself.