How to calculate and graph the recurrence of purchased products?

[RobertoMonzon]

I am working on power BI and I would like to know the recurrence in which companies acquire items or services.
Most of the posts I have seen are about the frequency of products purchased, that is, the number of products purchased in a given amount of time.
But I want to know every time a product is purchased, I hope that with the columns I add you can tell me how to do it, the truth is it is something I have never done. Below I leave more details of my table

I have a column called 'DESC_CC' containing the names of several companies, and another column called 'DESCRIPTION_ART_SER' with the names of items or services the companies acquire. Additionally, there's a column named 'APPROVED_DATE' indicating the approval date. If the 'NO_SIC' column is empty, it means the item/service was not approved. Moreover, there's a 'CANCELLED' column; if it contains a value, it signifies that the item/service was approved but subsequently canceled. If it has no value, it means it was not canceled.

Could you advise on setting up a recurrence for this scenario?

ID	DESCRIPTION_ART_SER	NO_SIC	Approved date	CANCELLED
1000242	ART1
1000245	ART2	1000242	5/29/2021 22:17
1000246	ART3
1000250	ART1
1000251	ART1	1000246	5/31/2021 11:42
1000252	ART3	1000247	5/31/2021 11:56
1000253	ART2	1000248	5/31/2021 12:07	Y
1000254	ART3	1000248	5/31/2021 12:07	Y
1000255	ART1	1000249	5/31/2021 12:15

[v-jianpeng-msft · Accepted solution]

@Greg_Deckler , thanks for your concern about this case. I tried to create a sample data myself based on the user's requirement and implemented the result. Please check if there is anything that can be improved. Here is my solution:

Hi, @RobertoMonzon 

Have you solved the current problem? Based on your description, if you want to calculate the recurrence of purchased products, you can create two calculated columns as follows:

Approved = IF(ISBLANK([NO_SIC]), "No", "Yes")

Cancelleda = IF(ISBLANK([CANCELLED]), "No", "Yes")

Then create a measure using the following DAX expression:

recurrence of purchased products =
COUNTAX (
    FILTER (
        'ECOM_PURCHASE_ORDER_DETAIL',
        'ECOM_PURCHASE_ORDER_DETAIL'[Approved] = "Yes"
            && 'ECOM_PURCHASE_ORDER_DETAIL'[Cancelleda] = "No"
    ),
    'ECOM_PURCHASE_ORDER_DETAIL'[DESCRIPTION_ART_SER]
)

Put this measure in the table visual:

If your situation doesn't allow you to create a calculated column, you can use a measure expression similar to the following:

MEASURE =
VAR _table =
    SUMMARIZE (
        ECOM_PURCHASE_ORDER_DETAIL,
        ECOM_PURCHASE_ORDER_DETAIL[ID],
        'ECOM_PURCHASE_ORDER_DETAIL'[DESCRIPTION_ART_SER],
        'ECOM_PURCHASE_ORDER_DETAIL'[NO_SIC],
        ECOM_PURCHASE_ORDER_DETAIL[APPROVED_DATE],
        ECOM_PURCHASE_ORDER_DETAIL[CANCELLED],
        "Approve", IF ( ISBLANK ( 'ECOM_PURCHASE_ORDER_DETAIL'[APPROVED_DATE] ), "No", "Yes" ),
        "cancell", IF ( ISBLANK ( 'ECOM_PURCHASE_ORDER_DETAIL'[CANCELLED] ), "No", "Yes" )
    )
VAR _count =
    COUNTAX (
        FILTER ( _table, [Approve] = "Yes" && [cancell] = "No" ),
        'ECOM_PURCHASE_ORDER_DETAIL'[DESCRIPTION_ART_SER]
    )
RETURN
    _count

Place this measure in the table visual:

I've provided the PBIX file used this time below.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

@RobertoMonzon Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[RobertoMonzon]

Hello

I just re edit the question.

I hope you can help me

I wrote this DAX code for a measure:

PromedioIntervaloCompra =

AVERAGEX(

    ADDCOLUMNS(

        SUMMARIZE(

            ECOM_PURCHASE_ORDER_DETAIL,

            ECOM_PURCHASE_ORDER_DETAIL[DESCRIPSION_ART_SER],

            "FechaAprobacion", MAX(ECOM_PURCHASE_ORDER_DETAIL[APPROVED_DATE])

        ),

        "Intervalo",

        VAR FechaActual = [FechaAprobacion]

        VAR FechaAnterior =

            CALCULATE(

                MAX(ECOM_PURCHASE_ORDER_DETAIL[APPROVED_DATE]),

                FILTER(

                    ECOM_PURCHASE_ORDER_DETAIL,

                    ECOM_PURCHASE_ORDER_DETAIL[DESCRIPSION_ART_SER] = EARLIER(ECOM_PURCHASE_ORDER_DETAIL[DESCRIPSION_ART_SER])

                        && ECOM_PURCHASE_ORDER_DETAIL[APPROVED_DATE] < FechaActual

                )

            )

        RETURN

            IF(ISBLANK(FechaAnterior), BLANK(), DATEDIFF(FechaAnterior, FechaActual, DAY))

    ),

    [Intervalo]

)