Measure - New sales after salesman's visit

[depple]

Hi,

I have this idea to a measure, that I have no clue on how to construct. I would like to measure new product sales, after a salesman's visit to a store. So for a product sale to count, the product has not been purchased by the store for e.g. the last 100 days before the visit, but been purchased after a visit.

Here is a mockup of my tables:

"Sales to store"
Date Store Product Qty
01.feb November Alfa 1
02.feb November Bravo 1
03.feb November Alfa 1
04.feb November Bravo 1
05.feb November Charlie 1
06.feb November Alfa 1
07.feb November Bravo 1
08.feb November Charlie 1

"Store Visit Date"
Date Store
04.feb November

The output of the measure in this example should be 2 (A=0, B=0, C=2).

I hope that I have adequately described the issue. Any help would be highly appreciated.

Regards,

depple

[Greg_Deckler · Accepted solution]

@depple - If you are talking about the total line, then that sounds like a measures total problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

[depple]

@Greg_Deckler ,

I implemented the formula from "The Final Word" and it works perfectly!

Just WOW!

Thank you for pointing me to the solution.

Regards,

/depple

[Greg_Deckler]

Awesome! It's a very popoular Quick Measure.

[v-yiruan-msft]

Hi @depple ，

I'm so sorry that I'm not clear about your requirement ... Is there any relationship between the table " Store Visit Date "  and the table " Sales to store" ? In addition ,  you mentioned that the measure value obtained should be 2 based on your sample data. I would like to ask how is this calculated ? what is your expected result ?

Best Regards

Rena

[depple]

---

@v-yiruan-msft wrote:

Hi @depple ，

I'm so sorry that I'm not clear about your requirement ... Is there any relationship between the table " Store Visit Date "  and the table " Sales to store" ? In addition ,  you mentioned that the measure value obtained should be 2 based on your sample data. I would like to ask how is this calculated ? what is your expected result ?

Best Regards

Rena

---

Hi @v-yiruan-msft ,

Thank you for your reply.

"Store" is the relationship between the two tables. The measure should return 2, because the only new product sales after the visit is 2xC. A and B should not be counted, as these were already being sold prior to the visit.

I hope that I have correctly interpreted your questions.

Regards,

depple

[v-yiruan-msft]

Hi @depple,

Thanks for your reply. You can create one measure just like the one in below screenshot:

1. Get the current product
2. Exclude the products whose sales date is earlier than the visit date . That is , the earliest sales date of the product is later than the visit date , then the sales quantity of this product will be aggregated .   

New sales =

VAR a =

    MAX ( 'Sales to store'[Product] )

VAR b =

    CALCULATE (

        SUM ( 'Sales to store'[Qty] ),

        FILTER (

            ALL('Sales to store'),

            'Sales to store'[Product] = a

                && MIN ( 'Sales to store'[Date] ) > MAX ( 'Store Visit Date'[Date] )

        )

    )

RETURN

    b

Best Regards

Rena

[depple]

Hi @v-yiruan-msft ,

Thank you for your suggestion. I am almost getting my head around understanding the formula, but the progress bar halts at approximately 80 % 🤔.

I have implemented your formula, but the values returned was first way too high. Realising that I have Page Level Filters set, with for instance Year=2020 (sales table goes way back), I am pretty sure that I am correct in changing FILTER ALL to FILTER ALLSELECTED. At least the values then returned were more reasonable.

But there is something really weird going on. A bigger problem is that I am completely lost in understanding what I am actually looking at. The listed products are not new sales, and the ones I know are, are not listed. I am not able to find the pattern. I have sat nearly an hour with this post now, looking back and forth between my tables, and I just don't get it. I am sorry for not being able to convey insight into what is wrong. Hence, I would completely understand if you do not wish to put more time into helping me.

Regards,

depple

[v-yiruan-msft]

Hi @depple,

It seems the following information is missing included in my formula, I just count the products after the sales visited...

"So for a product sale to count, the product has not been purchased by the store for e.g. the last 100 days before the visit, but been purchased after a visit."

If your issue still not be resolved, maybe you can share your pbix file with me, I will help check what's the cause. Thank you.

Best Regards

Rena

[depple]

Hi @v-yiruan-msft 

Thank you for your continued effort in trying to help me, and I am sorry for taking so long for answering.

Unfortunately, I am not able to share the .pbix, as it would take a lot of work to anonymize the data.

As written in an earlier post to @Greg_Deckler , his formula is working perfect and I am very happy with that. However, the formula does not sum new sales correctly. If it would be possible to include the Store "context" in the formula (I am in deep waters here), maybe the sum then would be calculated correctly. Nevertheless, I would understand it, it is difficult to help me without the .pbix or due to my poor ability to explain myself in proper terms.

Regards,

depple

[Greg_Deckler]

See if this works. Attached PBIX as well. See, just like I said...easy! 😉

Measure = 
    VAR __MostRecentVisit = MAXX(Visits,[Date])
    VAR __SalesTable = 'Sales'
    VAR __SalesAfterVisit = 
        ADDCOLUMNS(
            ADDCOLUMNS(
                SUMMARIZE(
                    FILTER(__SalesTable,[Date]>__MostRecentVisit),
                    'Sales'[Product],
                    "__Qty",SUM([Qty]),
                    "__Date",MIN([Date])
                ),
                "__Last Sale",
                    MAXX(
                        FILTER(
                            __SalesTable,
                            [Date]<=__MostRecentVisit &&
                                [Product] = EARLIER([Product])
                        ),
                        [Date]
                    )
            ),
            "__Diff",([__Date] - [__Last Sale]) * 1.
        )
RETURN
    SUMX(FILTER(__SalesAfterVisit,[__Diff] >= 100),[__Qty])

[depple]

Hi @Greg_Deckler 

Thank you for your suggestion. It was indeed a complex measure, at least at my level. 😅

The measure is correctly calculating the new sales quantity when used in a matrix with Store in the "Rows" (my interpretation from non-English language pack). But, it does not return the correct total quantity (see image below). Could the reason be that, since the Measure has no calculation on Store level, when I remove Store from the visual the measure checks for former sales among all stores, and not considering new sales for every store individually?

I see a huge possibility in me not explaining the issue in the 'correct' terms, but nevertheless I hope you can understand what I mean.

Thanks again for your efforts.

Regards,

depple

[Greg_Deckler]

Sounds sort of like a returning customer with a twist (date of sales person visit). So basically, get your store visit. Filter your fact table for everything after that date. ADDCOLUMNS to determine the MAXX previous date when that product was sold (EARLIER) and SUM the Qty.

[depple]

@Greg_Deckler

Thank you for your reply.

I probably should have mentioned that my DAX-knowledge is filled with gaps, and you suggestion easily falls between my areas of knowledge. I am nearly getting my head into what you are suggesting, but I am not near being able to start constructing the formula. Could you please elaborate on your answer?

Again, thank you for trying to help me.

Regards,

depple