MIN for specific filter

[Anonymous]

Hello everyone,

I have a bit of a hassle to find a way to calculate the time elapsed between the start date of a promotional campaign in which the company acquired new customers and the next purchases they made, also regarding other promotional campaigns.

There are 3 tables :

- Customers

- Promo campaigns

- Sales

Promotion table:

promo_start_date	promo_name
01.01.2017	A
03.02.2017	B
04.03.2017	C
05.07.2017	D

I defined, for each customer, which promo campaign they made their first purchase on.

Then, I am making a report in which the user can select the Promo Campaign using a slicer and then a table shows such results:

sales_date	promo_name	sales_amount
04.01.2017	A	15
05.02.2017	A	25
06.02.2017	B	10
07.03.2017	A	100
05.05.2017	D	200

All these sales are related to the Promo campaign on which the customers made their first purchase. But a customer can also make purchases on other campaigns but the goal is to know how much a specific Promo campaign made in revenues regarding the sales made, even on subsequent promotions.

The objective is to have such a table:

sales_date	promo_name	sales_amount	promo_start_date	NOT_promo_start_date
04.01.2017	A	15	01.01.2017	01.01.2017
05.02.2017	A	25	01.01.2017	01.01.2017
06.02.2017	B	10	01.01.2017	03.02.2017
07.03.2017	A	100	01.01.2017	01.01.2017
05.05.2017	D	200	01.01.2017	05.07.2017

Currently I have the NOT_promo_start_date, using this measure:

NOT_promo_start_date = 
CALCULATE(
    MIN(promotion[promo_start_date])
)

But this always returns the MIN for the current row. I also tested using ALLSELECTED, VALUES but the results are always wrong.

The goal is to get the promo_start_date of the selected Promo campaign selected by the user with the slicer, even for sales made on different promo campaigns but for which the first purchase of the customer was the selected Promo campaign of the slicer.

The ultimate goal is to have the time elapsed between the "acquiring" promo campaign and the current sale using
YEARFRAC(
    //Start date of the acquiring promo campaign
    , sales[sale_date]
    , 3
) * 12

Thanks in advance for your help !

[Anonymous · Accepted solution]

I find a way around, using other calculated columns.

In the 'customer' table, I added this calculated column:

date camp first sale = 
CALCULATE(
    MINX(
        RELATEDTABLE(promo_campaign);
        promo_campaign[promo_start_date]
    );
    FILTER(
        RELATEDTABLE(promo_campaign);
        promo_campaign[promo_name] = customer[promo first purchase]
    )
)

Then, in the 'sales' table a new calculated column:

time from promo start date = 
YEARFRAC(
    RELATED(customer[date camp first sale]);
    sales[sales_date];
    3
) * 12

This works but it is quite slow. If someone has any idea on how to solve this problem using measures, I would be pleased :).

[Anonymous]

Here are the two calculated columns I added in 'customer':

date first purchase = 
MINX(
    RELATEDTABLE(sales);
    sales[sales_date]
)

promo first purchase = 
IF(
    ISBLANK(customer[date first purchase]);
    BLANK();
    CALCULATE(
        MINX(
            RELATEDTABLE(sales);
            sales[promo_id_fk]
        );
        FILTER(
            RELATEDTABLE(sales);
            sales[sales_date] = customer[date first purchase]
        )
    )
)

[Anonymous]

I add some more details that may help for your understanding.

Your help will be much appreciated.

Thanks in advance.

[Anonymous]

@Anonymous, try diacivativing the relationship between your date and sales table and see if it works. 

[Anonymous]

Hi @Anonymous, unfortunately this doesn't work. And in the actual data I'm working on, the relationship between the Calendar and the sales table is not in both ways. But this seems to have no effect on the example I've provided.