Forum Discussion
Problem with PriorYear Counting measure
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
Wow - that's an impressive model. Lots of tables and links. :smileyhappy:
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....
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
14 Replies
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.
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.
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.
