Calculate follow up orders by product

[IAmAPowerBIUser]

Hi everybody,

I have a table of products with different customer orders, and when a user selects a specific product, I want to calculate the number of follow-up orders made by the same customers for different products, within a specified timeframe defined by a MonthsSlicer.

I have already written two DAX measures. They correctly calculate the total number of follow-up orders, but they don't display the correct results per ProductID. I need help refining these DAX measures to provide the correct numbers for each product.

This is my Product table:

 This are the results I obtain with my mesures:

 

This are the expected results:

 

I uploaded my .pbix file on wetransfer to better illustrate the exact situation: https://we.tl/t-yldWky0H9B

Thanks a lot for any help!

Best

Fabian

 

[AntrikshSharma]

@IAmAPowerBIUser When Car is selected how do you identify which row of Car in the table is the starting point? What is the logic behind Car = 1 in the visual?

[IAmAPowerBIUser]

Hi,

I realized that there was a mistake in my example calculations, sorry. For selecting the car all three products should have a count of 1.

So when car is selected I want to count for every product purchase how many cars there are purchased by the same customer between 1 and 13 months (selected by the months slicer) before the car purchase. On 01-feb-2023 fab purchased a car. The car purchase on 1-jan counts as 1. For the bike he purchased on 04-feb-2023 the car he purchased on 01-jan-2023 counts as 1. For the bus he purchased on 01-dec-2024 the car he purchased on 01-oct-2024 counts as 1.

[lbendlin]

when a user selects a specific product,

via the disconnected ProductID slicer?  What if they select more than one product, or no product?

within a specified timeframe defined by a MonthsSlicer.

Based on which anchor date?  The last purchase date for the selected product(s) ?

[IAmAPowerBIUser]

Hi,

thanks for asking questions for clarification. The product is indeed selected via the disconnected slicer which will be limited to single selection in the final version.

The anchor dates are all purchase dates for the selected product. So I want to check for all purchases of the product, not only the most recent one.

[v-bmanikante]

Hi @IAmAPowerBIUser ,

Could you please provide a clear explanation of your requirement along with the expected result? A bit more context will help us understand what you're trying to achieve and suggest the best approach in Power BI.

Regards,

B Manikanteswara Reddy

[IAmAPowerBIUser · Accepted solution]

Hi,

thanks everyone for your support!
I finally came up with a DAX measure myself that calculates the correct solution. If in the future anyone is struggeling with a similar problem this is the solution:

ProductOrdersAfterSelected_04 =

VAR SelectedMinMonth = MIN(MonthsSlicer[Months])

VAR SelectedMaxMonth = MAX(MonthsSlicer[Months])

VAR SelectedProduct = SELECTEDVALUE(ProductSlicer[ProductID])

RETURN

SUMX (

    FILTER (

        'Product',

        NOT ISBLANK('Product'[ProductID])

    ),

    VAR CurrentCustomer = 'Product'[CustomerID]

    VAR CurrentDate = 'Product'[PurchaseDate]

    VAR HasCarBefore =

        CALCULATE (

            COUNTROWS ( 'Product' ),

            FILTER (

                ALL ( 'Product' ),

                'Product'[CustomerID] = CurrentCustomer &&

                'Product'[ProductID] =  SelectedProduct &&

                'Product'[PurchaseDate] < CurrentDate &&

                'Product'[PurchaseDate] >= EDATE ( CurrentDate, -SelectedMaxMonth ) &&

                'Product'[PurchaseDate] <= EDATE ( CurrentDate, -SelectedMinMonth )

            )

        )

    RETURN  HasCarBefore

)