Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Computing values between a range of dates

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 consider...
Need Help
danextian's avatar
danextian
Icon for Super User rankSuper User
2 years ago

Hi Anonymous ,

 

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 )
)

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.