Create column based on lign value and interval from another table

[Anonymous]

Hello everyone!

I have been trying to resolve this issue for some time now but I'm stuck.

I have two tables : 

--> One describing sales for different products on specific days

Product	Day	Sales
A	1	50
A	2	40
A	2	20
A	4	20
A	5	30
B	1	20
B	3	30
B	5	40
B	6	50
B	8	20
C	1	50
C	4	20
C	4	10

--> And another one listing for each product the beginning of "seasons" (different products don't have the same season beginning):

Product	start_new_season
A	0
A	3
A	5
A	10
B	0
B	2
B	4
C	0
C	4

What I need is to add a column on my first table that specify the day of beginning of season the sales have been made in : 

Product	Day	Sales	start_new_season
A	1	50	0
A	2	40	0
A	2	20	0
A	4	20	3
A	5	30	5
B	1	20	0
B	3	30	2
B	5	40	4
B	6	50	4
B	7	20	4
C	1	50	0
C	4	20	4
C	4	10	4

Thanks !

[Anonymous]

Thanks @VahidDM and @speedramps ! Your help is really appreciated. I'll go for your recommandation Vahid and I took note of your remark speedramps

[VahidDM · Accepted solution]

Hi @Anonymous 

Try this code to add a new column with dax to the sale table:

Start_New_Sale = 
CALCULATE (
    MAX ( 'product'[start_new_season] ),
    FILTER (
        ALL ( 'product' ),
        'product'[Product] = EARLIER ( sales[Product] )
            && 'product'[start_new_season] <= EARLIER ( sales[Day] )
    )
)

output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[speedramps]

Good answer VahidDM  but may I mention a potential problem.

Product C has two sales on day 4 (20 + 10 = 30).

If the user use the default sums for sales by product by day then it will returns a Start_New_Sale sum of 4+4 =8 instead of 4 .

So the users should alway display the value as min or max to return 4 rather than use default sum.

[VahidDM]

@speedramps

That's not a problem, you mentioned a question that has a clear answer!

that can happened for any columns with different values and different aggregation, and you need to make sure to use your data with correct aggregation!!

Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[speedramps]

Hi jlwork

Click here to download an example solution 

Please consider this solution.

I am a Power BI volunteer. Please click the thumbs up for me going to the effort of trying to helping you. Then click solved if I fix your problem. One problem per ticket please. If you need to expand or change your problem then please raise a new ticket and click solved on this one so we get kudos. Many thanks. 

Start of new season =

VAR myproduct = SELECTEDVALUE(Sales[Product])

VAR myday = SELECTEDVALUE(Sales[Day])

RETURN

CALCULATE(

MAX(Seasons[start_new_season]),

FILTER(Seasons,

Seasons[Product] = myproduct

&& Seasons[start_new_season] <= myday)

)

[Anonymous]

Hi @speedramps ! Thank you for your answer ! Do you think there is a way to have the results as a column and not as a measure ?

[speedramps]

Hi again jlwork

It will be very difficult to to get a column because a product can have multiple season start dates and multiple sale dates,
eg product A seasons = 3, 5 and 10 and sales dates = 1,2,3,4,5.
So for sales date 4 you have to scan all the seasons to get the answer 3.
That is easy in Dax but difficult in Power Query.

You didnt originally specify it must be column.

Please click the thumbs up for me going to the effort of trying to helping you and click solved for the DAX solution so I get kudos. Many thanks.