Getting all subsequent sales after slicer choice

[AndersDonker]

Dear forum,

It will be my first post here after working for over 4 years with PowerBI. I came across a challenge which I can't seem to solve by myself so I could really use your help!

The premis

I have created a very simple (fictive) dataset to define my challenge. The dataset contains two tables:

- Products which is a lookup table 

ProductName	ProductID	ProductGroup
Product A	1	Group A
Product B	2	Group A
Product C	3	Group A
Product D	4	Group A
Product E	5	Group B
Product F	6	Group B
Product G	7	Group B
Product H	8	Group B

- Sales is the table where all the sold products are registered per customer

ProductID	CustomerID	Sequence	SaleDate
1	1	2	2022-02-01
2	1	3	2022-03-01
3	1	4	2022-04-01
4	1	1	2022-01-01
7	1	5	2022-05-01
6	2	3	2022-04-01
7	2	4	2022-06-01
8	2	1	2022-01-01
2	2	2	2022-03-01
6	3	1	2022-06-01
2	3	2	2022-07-01
5	4	5	2022-10-01
8	4	2	2022-07-01
2	4	1	2022-06-01
4	4	3	2022-08-01
1	4	4	2022-09-01

Here a PowerBI datamodel view for the relation between the two:

The challenge
I want to have a slicer on productname and once the user has selected a product, i would like to see the subsequent productsales that have been done.

Here is a visualisation of what i am tryng to accomplish:

The multicolored table is the Sales table with three scenarios.

The yellow table is the result of the slicer choice

The green table is the desired output

I have tried a dynamic table and also tried to search for comparing scenario's online but was not succesfull.

Here is the link to the source excel file and pbix file. 

PowerBI and Excel file 

Thanks in advanced!

With regards,

Anders Donker

[tamerj1 · Accepted solution]

Hi @AndersDonker 
Is this what you're looking for?  Please see attached file.

Count = 
VAR SelectedProduct =
    MAXX (
        CALCULATETABLE (
            VALUES ( Products[ProductID] ),
            ALLSELECTED ( Products[ProductName] )
        ),
        Products[ProductID]
    )
RETURN
    SUMX (
        CALCULATETABLE (
            SUMMARIZE (
                Sales,
                Products[ProductName],
                Sales[CustomerID],
                Sales[Sequence],
                Sales[SaleDate]
            ),
            ALL ( Products[ProductName] )
        ),
        VAR CurrentDate = Sales[SaleDate]
        VAR CurrentCustomerTable =
            CALCULATETABLE ( Sales, ALLEXCEPT ( Sales, Sales[CustomerID] ) )
        VAR T1 =
            FILTER ( CurrentCustomerTable, Sales[ProductID] = SelectedProduct )
        VAR ProductDate =
            MAXX ( T1, Sales[SaleDate] )
        RETURN
            IF ( CurrentDate > ProductDate, 1 )
    )

[CNENFRNL · Accepted solution]

_ = 
VAR __id =
    CALCULATE( MAX( SALES[CustomerID] ), ALLEXCEPT( SALES, SALES[CustomerID] ) )
VAR __ds =
    CALCULATETABLE(
        SUMMARIZE( SALES, SALES[CustomerID], SALES[Sequence] ),
        ALLEXCEPT( SALES, PROD[ProductName] )
    )
VAR __seq =
    MAXX( FILTER( __ds, SALES[CustomerID] = __id ), SALES[Sequence] )
RETURN
    IF(
        NOT ISBLANK( __seq )
            && __seq
                < CALCULATE( MAX( SALES[Sequence] ), ALLEXCEPT( SALES, SALES[Sequence] ) ),
        ""
    )

[tamerj1 · Accepted solution]

Hi @AndersDonker 
Here is another solution I guess as per your requirement. Using a slicer table.

Count = 
VAR SelectedProduct =
    MAX ( 'Product Slicer'[ProductID] )
VAR FilteredTable = 
    FILTER ( ALL ( Sales ), Sales[ProductID] = SelectedProduct )
VAR Result =
    SUMX ( 
        Sales,
        VAR CurrentCustomer = Sales[CustomerID]
        VAR SelectedDate = 
            MAXX ( 
                FILTER ( FilteredTable, Sales[CustomerID] = CurrentCustomer ), 
                Sales[SaleDate] 
            )
        RETURN 
            IF ( 
                Sales[SaleDate] > COALESCE ( SelectedDate, TODAY ( ) ),
                1
            )
    )
RETURN
    Result

[AndersDonker · Accepted solution]

You expert users are exceptional in your speed of response and explanations!

My compliments for the results. I have received two solutions for a single problem. Thanks! I will accept both solutions.

With regards,

Anders Donker

[tamerj1 · Accepted solution]

Hi @AndersDonker 
Here is another solution I guess as per your requirement. Using a slicer table.

Count = 
VAR SelectedProduct =
    MAX ( 'Product Slicer'[ProductID] )
VAR FilteredTable = 
    FILTER ( ALL ( Sales ), Sales[ProductID] = SelectedProduct )
VAR Result =
    SUMX ( 
        Sales,
        VAR CurrentCustomer = Sales[CustomerID]
        VAR SelectedDate = 
            MAXX ( 
                FILTER ( FilteredTable, Sales[CustomerID] = CurrentCustomer ), 
                Sales[SaleDate] 
            )
        RETURN 
            IF ( 
                Sales[SaleDate] > COALESCE ( SelectedDate, TODAY ( ) ),
                1
            )
    )
RETURN
    Result

[AndersDonker]

Hi @tamerj1 , i decided to go forward with your solution as it gives me the most freedom to reuse the dimensions in the Products table as in the real world i have plenty of dimensions in the Products table which endusers wish to filter upon. 

I can follow along your solution quite well until i reach the final if statement with the coalesce. Can you elaborate why you are using coalesce with TODAY() and why it is needed? I have trouble comprehending why it's needed. If i remove it from the final solution i get very unexpected results but don't understand why.

Also, i plan to reuse this measure to calculate the difference in months between the product selected in the slicer and the results produced for subsequent products sold. Any suggestions on how I could reuse this? 

My plan is to add a slicer with a numerical input between 1 and 12 to give the endusers the freedom to limit the period of subsequent sales. My guess is that the TRUE result of the IF statement, the 1, could be replaced with a datediff function to determine the datediff in months for every row i will have half of my challenge solved. What are your thoughts about that? 

With regards and much respect for the given solution (!!),

Anders Donker

[tamerj1]

Hi  @AndersDonker 
Regarding the usage of COALESCE 

VAR SelectedDate =
    MAXX (
        FILTER ( FilteredTable, Sales[CustomerID] = CurrentCustomer ),
        Sales[SaleDate]
)

If the table which MAXX iterates over is empty then the valiable value would be blank. 

IF (
    Sales[SaleDate] > COALESCE ( SelectedDate, TODAY ( ) ),
    1
)

Now if the value is blank then the IF condition will return TRUE as the Sales[SaleDate] will always be greater than blank. Therefore I return Today being greater than the greatest available date in the sales table in order to force FALSE over this condition as selected date do not actually exist. You need to imagine that.

For the other part please post a new question with all the details including example of desired results.

[CNENFRNL · Accepted solution]

_ = 
VAR __id =
    CALCULATE( MAX( SALES[CustomerID] ), ALLEXCEPT( SALES, SALES[CustomerID] ) )
VAR __ds =
    CALCULATETABLE(
        SUMMARIZE( SALES, SALES[CustomerID], SALES[Sequence] ),
        ALLEXCEPT( SALES, PROD[ProductName] )
    )
VAR __seq =
    MAXX( FILTER( __ds, SALES[CustomerID] = __id ), SALES[Sequence] )
RETURN
    IF(
        NOT ISBLANK( __seq )
            && __seq
                < CALCULATE( MAX( SALES[Sequence] ), ALLEXCEPT( SALES, SALES[Sequence] ) ),
        ""
    )

[AndersDonker]

Hi @CNENFRNL, 

Thanks for the quick answer. You managed to pull off exactly what I asked for, however, my initial questioning was not adequate.

The desired output would be the counts of subsequent products per productname.

So when selecting Product B from the slicer. I would like to see the following:

Count = 8 --> as 8 subsequent products sold.

And i would like to re-use the Products lookup table to give some additional depth to the Subsequent productcount.

So once the dax measure is added to a chart or a table with the addition of ProductGroup and ProductName. 

Resulting into the following table:

Products.ProductName	Products.ProductGroup	Count
Product A	Group A	1
Product C	Group A	1
Product D	Group A	1
Product E	Group B	1
Product F	Group B	1
Product G	Group B	2
Product H	Group B	1

[CNENFRNL]

For fun only, a showcase of powerful Excel worksheet formulas,

PBI solution is way much trickier than you can imagine,

 

[AndersDonker]

Your PowerBI solution seems to do what i had in mind and i was fully aware that it would be quite complex! thats why i posted my first forum question!

Could you also upload the PowerBI solution so i can review? 

[CNENFRNL]

pbix file is uploaded now. Enjoy such a brain teaser, enjoy DAX!

[tamerj1 · Accepted solution]

Hi @AndersDonker 
Is this what you're looking for?  Please see attached file.

Count = 
VAR SelectedProduct =
    MAXX (
        CALCULATETABLE (
            VALUES ( Products[ProductID] ),
            ALLSELECTED ( Products[ProductName] )
        ),
        Products[ProductID]
    )
RETURN
    SUMX (
        CALCULATETABLE (
            SUMMARIZE (
                Sales,
                Products[ProductName],
                Sales[CustomerID],
                Sales[Sequence],
                Sales[SaleDate]
            ),
            ALL ( Products[ProductName] )
        ),
        VAR CurrentDate = Sales[SaleDate]
        VAR CurrentCustomerTable =
            CALCULATETABLE ( Sales, ALLEXCEPT ( Sales, Sales[CustomerID] ) )
        VAR T1 =
            FILTER ( CurrentCustomerTable, Sales[ProductID] = SelectedProduct )
        VAR ProductDate =
            MAXX ( T1, Sales[SaleDate] )
        RETURN
            IF ( CurrentDate > ProductDate, 1 )
    )

[AndersDonker]

Hi @tamerj1  i have the same response to your solution as for CNENFRNL.

Thanks for the quick answer. You managed to pull off exactly what I asked for, however, my initial questioning was not adequate.

The desired output would be the counts of subsequent products per productname.

So when selecting Product B from the slicer. I would like to see the following:

Count = 8 --> as 8 subsequent products sold.

And i would like to re-use the Products lookup table to give some additional depth to the Subsequent productcount.

So once the dax measure is added to a chart or a table with the addition of ProductGroup and ProductName. 

Resulting into the following table:

Products.ProductName	Products.ProductGroup	Count
Product A	Group A	1
Product C	Group A	1
Product D	Group A	1
Product E	Group B	1
Product F	Group B	1
Product G	Group B	2
Product H	Group B	1

Furthermore, what is the standard policy for accepting the solution? You both managed to provide me exactly what i asked for. Unfortunately my question was not adequate enough. Should i accept both your solutions? Or should i accept the solution once my additional question is answered?

Thanks!

[AndersDonker]

One small addition:

I would like to have a dynamic table or a DAX measure that counts the subsequent Sales per productname as i am not sure if a dynamic table would work in a tabular model.

Thanks!