Computing values between a range of dates

[Pedro503]

Hey there

Here's the link for my sample file.

How can I compute the amount of sales for a given user, but respecting their active date period? For instance, taking the IdUser = 1 into consideration a correct measure should computes the total of 278, as his active period (shown in d_users table) is from January 1st, 2020 to February 19th, 2020, therefore excluding the sales that took place after this period, i.e., IdSale = 16 and IdSale = 19.

For this specific case, I've authored the following measure, but it has not worked in the total level, only in the row level.

Modelo = 
VAR _Maximo = MAX( d_calendar[Date] )
VAR _Minimo = MIN( d_calendar[Date] )
VAR _Periodo =
    DATESBETWEEN(
        d_calendar[Date],
        CALCULATE( MIN( d_users[DateStart] ) ),
        CALCULATE( MIN( d_users[DateEnd] ) )
    )
VAR _UsersAtivos =
    SUMMARIZE(
        FILTER(
            d_users,
            d_users[DateStart] <= _Maximo
                && d_users[DateEnd] >= _Minimo
        ),
        d_users[IdUser]
    )
VAR _Resultado =
    CALCULATE(
        SUMX( f_sales, f_sales[Amount] ),
        KEEPFILTERS( _UsersAtivos )
    )
RETURN
    _Resultado

The other calculation I'd like to compute is basically the same, however now the date period is stored at f_users. In this calculation, only the IdSale = 16 should not be take into consideration as the IdSale = 19 took place in his second active period at f_users.

Could you guys help me out with this? @SpartaBI @Ahmedx @Ritaf1983 

Thanks in advance.

[v-shex-msft]

Hi @Pedro503 ,

Any update for this? Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information and description to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[Pedro503]

Hey there.

I've tried all the responses, but none of them actually worked.

[danextian]

Hi @Pedro503 ,

Please try this:

Sales within active period = 
CALCULATE (
    SUM ( f_sales[Amount] ),
    FILTER (
        f_sales,
        f_sales[DateSale] >= MIN ( d_users[DateStart] )
            && f_sales[DateSale] <= MAX ( d_users[DateEnd] )
    ),
    CROSSFILTER ( d_users[IdUser], f_sales[IdUser], NONE )
)

[Pedro503]

Hey, @danextian . Thanks a lot for your response.

I've tried replicatig this measure you provide but there are some row-level numbers that don't seem to be right, as outlined in the following image. In this specific case, the number 93 is a sale from IdUser = 1, but in this week this user was not active. Do you know why it happens? However, you manage to fix the total-level result, which is great for my case. Thanks for that.

Also, do you know how to make the calculation for the 'f_user' table? There there are more than one row for each IdUser, therefore making things even more difficult (which unfortunely happens in my real-life model).

Once again, I do appreciate your help.

[Ashish_Mathur]

Hi,

Darag this measure to the visual

Modelo1 = SUMX(VALUES(d_calendar[Week]),[Modelo])

Hope this helps.

[Pedro503]

Hey, @Ashish_Mathur . Thanks for your response.

Great, it has actually worked.

Do you know how to compute the second part?

The other calculation I'd like to compute is basically the same, however now the date period is stored at f_users. In this calculation, only the IdSale = 16 should not be take into consideration as the IdSale = 19 took place in his second active period at f_users.

My real-life model has this particularity, but I don't know how to solve this either.

Once again, thanks for your response.

[Ashish_Mathur]

I just cannot understand your requirement.  Share a simple dataset and show the expected result.