Count the number of zero sales over the most recent n-week time period

[jlankford]

Sample Set Here

I'm having difficulty adding up the number of times a particular store location has zero sales over a 4-week period for a particular product. 

See the example above, what I need is a matrix that shows the audience only two things:

1.) The product in the row dimensions

2.) The number of stores where the most recent 4 weeks have shown zero sales

That's it. 

I have tried the following:

1.) Created a custom column where if the week is one of the most recent 4, then the value is 1, else, 0.

2.) Created a measure counting the number of stores where sales quantity = 0 AND the custom column value =1.

The result was that I got the count of stores that had zero sales during any of the 4 weeks. I need to show stores that have had zero sales for the entirety of the 4 weeks. 

So - if Store A, over the last 4 weeks, had one sale 4 weeks ago, and zero sales thereafter, it should not count; If store B over the last 4 weeks sold nothing, it should count. 

Please let me know if you have enough information to help. Thank you so much in advance!

[OwenAuger]

Hi @jlankford 

Sharing doesn't seem to be enabled for the link you sent through - could you turn on sharing?

The basic approach I would suggest is to create a measure that iterates over a Customer dimension and for each customer filters to those with zero sales in the last 4 weeks (by applying a date filter).

[jlankford]

Hi again, 

I'm either not skilled enough to do this right, or the method you suggested is not giving me a correct answer. 

Something like:

LAST 4 WEEK SUM = CALCULATE(SUM(Master[POS Sales Dollars]), FILTER(Weeks,DATESBETWEEN(Weeks[WM Week,MAX(Weeks[WM Week]-4,MAX(Week[WM Week]))))

This accurately gives me the sum of the recent 4 weeks. 

Then, i will do something like:

COUNT ZERO WEEKS = CALCULATE(COUNT(Master[Store NBR]),[LAST 4 WEEK SUM] = 0)

This will only give me all the times when the entire product has zero sales over the past 4 weeks across every store. So:

If a product even sells one unit out of 4800 stores, then it won't count it

If a product is discontinued and for some reason still in my dataset, it's the only one counted.

[jlankford]

Does anyone have any ideas on what I might be doing wrong?

[OwenAuger · Accepted solution]

Hi @jlankford 

I have uploaded a suggested solution here:

PBIX link

I recommend you create dimensions for Week, Store & Product. These sorts of calculations are safer when you have separate dimensions rather than relying on values being present in the fact table. I'm anticipating that you could have cases where a store sold zero of a particular product and no row exists rather than having an explicit zero value.

The final measure for counting number of stores with zero sales is:

Number of Stores with Zero Sales (recent 4 weeks) =
VAR MaxWeek =
    MAX ( Week[Week No] )
VAR WeekFilter =
    TREATAS ( GENERATESERIES ( Maxweek - 3, MaxWeek ), Week[Week No] )
    // Convenient way of generating list of last 4 weeks

RETURN
    COUNTROWS (
        FILTER (
            Store,
            CALCULATE ( SUM ( DATA[sales] ), ALL ( Week ), WeekFilter ) = 0
        )
    )

Regards,

Owen

[jlankford]

Thank you for taking the time and helping me out! The numbers on my huge dataset are correct, and your method of solving this makes perfect sense, and frankly, I feel a little silly for not thinking of it earlier. 

This was a massive help. Thank you!

[jlankford]

Thank you for your response, and I'm sorry for not sharing sooner. 

https://drive.google.com/file/d/1jC2cg5XwLxyi24x2jW-0yUu8qLGlfuxc/view?usp=sharing

This will point you to the file. 

Thanks again - I will try to work out a way with your solution.