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

DAX DAX Calculations
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