Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 !
Solved! Go to 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/
Thanks @VahidDM and @speedramps ! Your help is really appreciated. I'll go for your recommandation Vahid and I took note of your remark speedramps
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/
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.
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/
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.
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 ?
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.