Count stores with orders of same product in more than 1 week

DAX DAX Calculations
1

Here’s a fun one :wink: I created a mockup of data so that I can illustrate what I’m trying to do and post the PBIX.

I have 10 stores that sell fruits and vegetables, and I can see their orders aggregated by week. I want to count the number of stores with re-orders. In other words, count the number of stores that ordered the same product again after their initial order, and I want to view this on a cumulative weekly basis.

As you can see in the pbix, my DAX measured are correctly counting the number of stores that placed an order in the 3 week period (9 of 10 stores ordered fruit) However, I can’t get the re-order expression to work. If it worked correctly, it would show that on a cumulative basis, 1 store re-ordered any fruit product in week ending Jan 9 and 6 stores had re-ordered any fruit product in week ending Jan 16th. (see attached picture for illustration)

Here’s the incorrect DAX formula that I tried:
# of Stores with Re-Orders =
calculate(
calculate(distinctcount(Orders[Store#]),
filter(values(StoreList[Store #]), DISTINCTCOUNT(Orders[WeekEndDate])>1) )
, filter(all(Orders[WeekEndDate]), Orders[WeekEndDate] <= MAX( Orders[WeekEndDate] ) ) )

fruitsJPG
fruitsJPG295×815 43.5 KB
Orders and Re-Orders Sample.pbix (28.2 KB)

2

Hi @Eric! We noticed that your inquiry was left unsolved for quite some time now. We strive to answer ALL inquiries in the forum. However, if you are sure that you provided all pertinent context to your concerns and read how to use the forum more effectively and still find your question unanswered, you can check out tutorials to learn this yourself as your membership also comes with relevant resources that may help you with your Power BI education.

While our users and experts do as much as reasonable to help you with your inquiries, not all concerns can be attended to especially if there are some learnings to be done. Thank you!

3

@Eric,

You did everything right here - clear explanation of the problem, PBIX file provided, etc. I have no idea how this one slipped through the cracks. I will give this my full attention today right after work and attempt to get you a solution ASAP.

Thanks for your patience.

  • Brian
4

@Eric,

Wow - I can see why this one gave you trouble. It doesn’t look overly complicated, but it turns out to be a beast. When I popped the hood on the data model, the root cause looked obvious:

Snag_9903a18
Snag_9903a18695×510 129 KB

No Date Table, without which time intelligence-based measures are not going to work. However, even after installing a top-of-the-line date table in your model, and marking it as such, there are still significant hurdles to overcome, namely:

  1. granularity mismatch - the date table operates at a daily granularity, while the fact table operates at a weekly granularity. To get around this problem, I built out all the measures using a virtual relationship via TREATAS at the week level.
  2. a lot of complexity in both the virtual table components and the filter context of the necessary measures.

Here are the two main measures for my solution thus far:

Num Stores Purchasing = 

VAR HarvestMultiple = VALUES(  Orders[Category] )

VAR NumStoresPurch = 
CALCULATE(
    DISTINCTCOUNT( Orders[Store#] ),
    FILTER(
        Orders,
        Orders[Category] IN HarvestMultiple
    ),
    TREATAS( DISTINCT( Dates[WeekEndingAdj] ), Orders[WeekEndDate] )
)

//This table is not yet used in the solution, but is useful for debugging purposes.
VAR vTable =
ADDCOLUMNS(
    SUMMARIZE(
        ALLSELECTED( Orders ),
        Orders[Store#],
        Orders[WeekEndDate]
    ),
    "@NumStores", NumStoresPurch
)

RETURN
NumStoresPurch

Stores Purchasing = 

VAR HarvestMultiple = VALUES(  Orders[Product] )
VAR SelWeek = SELECTEDVALUE( Dates[WeekEndingAdj] )

VAR StoresPurch = 
CALCULATE(
    CONCATENATEX(
                SUMMARIZE(
                ALLSELECTED(Orders),
                Orders[Store#]
               // Orders[WeekEndDate]
            ),
            
        Orders[Store#],
        ", ",
        Orders[Store#],
        ASC
    ),
    TREATAS( DISTINCT( Dates[WeekEndingAdj] ), Orders[WeekEndDate] )
)
RETURN
// IF( HASONEVALUE( Dates[WeekEndingAdj] ), StoresPurch, BLANK() )
StoresPurch

Here’s what it looks like put together:

image

Before I proceed to a full solution, I need to better understand what is and what is not considered a reorder.

In the case of Store #1 for example:

image

Would this be considered a reorder or not? (since they did repurchase fruit, but not in the week immediately subsequent to the initial purchase). Once I have a bit more clarity on the requirement, I can proceed on to a full solution.

I hope this is helpful.

  • Brian
2 Likes
5

Hi @Eric, did the response provided by @BrianJ help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

6

Hi @Eric, we’ve noticed that no response has been received from you since the 30th of January. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

7

Hi @Eric, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.

We’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!