Forum Discussion
DistinctCount with date criteria
Hi All,
I have been trying to create a DAX measure that counts the unique number of outlets that are out of stock in the current month i.e. had stock any point in the previous month.
Below is an example of my data
AuditDate OutletID ProductName StockQuantity
15-Jul-17 1001 Candles 0
31-Jul-17 1002 Candles 1
31-Jul-17 1001 Candles 1
12-Aug-17 1001 Candles 0
31-Aug-17 1002 Candles 1
From the above example only outlet "1001" is out of stock in the month of August
Can do this in regular excel, but cant seem to replicate in DAX
Your help will be greatly appreciated.
Thank you
Hi All,
I have been trying to create a DAX measure that counts the unique number of outlets that are out of stock in the current month i.e. had stock any point in the previous month.
Below is an example of my data
AuditDate OutletID ProductName StockQuantity
15-Jul-17 1001 Candles 0
31-Jul-17 1002 Candles 1
31-Jul-17 1001 Candles 1
12-Aug-17 1001 Candles 0
31-Aug-17 1002 Candles 1
From the above example only outlet "1001" is out of stock in the month of August
Can do this in regular excel, but cant seem to replicate in DAX
Your help will be greatly appreciated.
Thank you
10 Replies
Hi,
Try this formula
=COUNTROWS(FILTER(SUMMARIZE(VALUES(Data[Outlet ID]),[Outlet ID],"ABCD",SUM([Stock Quantity]),"EFGH",CALCULATE(SUM([Stock Quantity]),PREVIOUSMONTH('calendar'[Date]))),[ABCD]=0&&[EFGH]>0))Hi Ashish_Mathur thanks for your response.
Formula works fine, but I get a blank output when I place in my pivot table, its possibly because I have multiple products on my actual table.
How can I edit the measure to work with multiple products
Thank you.
Hi,
That should not matter. Share the link from where i can download your file. Also, show the exact problem that you are facing.
Hi ChumaAmako,
If I understand you correctly, you should be able to use the formula below to create a new calculate column in your table to indicate if the outlet had any stock in the previous month.
StockQuantityLastMonth = CALCULATE ( SUM ( Table1[StockQuantity] ), FILTER ( Table1, Table1[OutletID] = EARLIER ( Table1[OutletID] ) && Table1[AuditDate] <= EOMONTH ( EARLIER ( Table1[AuditDate] ), -1 ) && Table1[AuditDate] >= EOMONTH ( EARLIER ( Table1[AuditDate] ), -1 ) - DAY ( EOMONTH ( EARLIER ( Table1[AuditDate] ), -1 ) ) + 1 ) )Then you can use the formula below to create a new measure to get distinct count of the unique number of outlets that are out of stock in the current month and had stock any point in the previous month. :smileyhappy:
Measure = CALCULATE ( DISTINCTCOUNT ( Table1[OutletID] ), FILTER ( Table1, Table1[StockQuantity] = 0 && Table1[StockQuantityLastMonth] > 0 ) )Regards
Hi v-ljerr-msft
Thanks so much for your help.
I love your approach in creating a calculated column first. However I get the below error message when I try.
"Can Not Nest Earlier/Earliest Functions"
This is the formula I have:
=CALCULATE(SUM(StockCount[Total Stock Count]),FILTER(StockCount,StockCount[Outlet ID]=EARLIER(StockCount[Outlet ID]&&StockCount[Date of Audit]<=EOMONTH(EARLIER(StockCount[Date of Audit]),-1)&&StockCount[Date of Audit]>=EOMONTH(EARLIER(StockCount[Date of Audit]),-1)-DAY(EOMONTH(EARLIER(StockCount[Date of Audit]),-1)),+1)))
Is there a way out of this?
One more thing, if i have multiple products, will an additional "&&TableName[ProductName]" inclusion in the Earlier formula work?
Thanks once again for your response.
Hi All,
I have been trying to create a DAX measure that counts the unique number of outlets that are out of stock in the current month i.e. had stock any point in the previous month.
Below is an example of my data
AuditDate OutletID ProductName StockQuantity
15-Jul-17 1001 Candles 0
31-Jul-17 1002 Candles 1
31-Jul-17 1001 Candles 1
12-Aug-17 1001 Candles 0
31-Aug-17 1002 Candles 1
From the above example only outlet "1001" is out of stock in the month of August
Can do this in regular excel, but cant seem to replicate in DAX
Your help will be greatly appreciated.
Thank you
