DistinctCount with date criteria

[ChumaAmako]

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

[v-ljerr-msft]

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. 

Measure =
CALCULATE (
    DISTINCTCOUNT ( Table1[OutletID] ),
    FILTER (
        Table1,
        Table1[StockQuantity] = 0
            && Table1[StockQuantityLastMonth] > 0
    )
)

Regards

[ChumaAmako]

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.

[Ashish_Mathur]

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

[ChumaAmako]

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.

[Ashish_Mathur]

Hi,

That should not matter.  Share the link from where i can download your file.  Also, show the exact problem that you are facing.

[ChumaAmako]

Hi @Ashish_Mathur

You are absolutely right. It WORKED!!!

I saw so many blank cells and assumed it didnt work, but when I consolidated the data in the pivot, I noticed that there only a few outlets running out of stock to my surpise.

You can access the data HERE

Can I bother you for one more thing.

How can I know the total stock count (for all SKU ID's) for just the outlets that ran out of stock.

Thanks so much for your help.

You Rock!!!

[Ashish_Mathur]

Hi,

You are most welcone.  Thank you for your kind words.  I do not understand your second question.  Please share some data and show the expected result.

[ChumaAmako]

Ok.

From your last solution, we could identify which outlets have run out of stock.

We need to calculate the importance of these outlets by summing up their total stock holding of all SKU's

A satisfactory output will be to have the total stock (i.e. for all SKU's seen in the outlet - which has run out of stock for any particular SKU) as at the last date of visit

You can find the powerpivot I am working on here: https://drive.google.com/file/d/0Byxc8IzPhZ5-eGpyWmQ4Wk9FZlE/view?usp=sharing

Thanks so much as usual

[Ashish_Mathur]

Hi,

This calculated field formula will give you the stock as at the precious month end

=CALCULATE(SUM([Total Stock Count]),PREVIOUSMONTH('Calendar'[Date]))

Filter the Pivot Table on where the Outlets out of stock is > 0

[ChumaAmako · Accepted solution]

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