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.
Hi all,
Here is the problem description.
I have a column of product names A,B,C,D....
I have entries of product sales revenue from each day. (colums of product names, date of sales, sales revenue value)
I would like to have another column to show when e.g. accumulated product A sales reaches 1M. (The date it becomes a so-called mature product in the market.)
How I should formulate the DAX in a calculated field / a customized column?
Thanks in advance.
Solved! Go to Solution.
Please check the file attached in my earlier post
It works with the slicer for Products
Hi @Qianru221
Try this
First Add a Measure
Cumulative = SUMX ( FILTER ( ALL ( TableName[ Date ] ), TableName[ Date ] <= SELECTEDVALUE ( TableName[ Date ] ) ), CALCULATE ( SUM ( TableName[ Revenue ] ) ) )
Then you can get the first date sales reach 1 Miilion using this MEASURE
Date when 1 Million reached = TOPN ( 1, FILTER ( ALL ( TableName[ Date ] ), [Cumulative] > 1000000 ), [Cumulative], ASC )
Hi,
Thank you for your help!
Would it be possible to have it for each product A,B,C,D....? Cos each product have a culmulative revenue, and the date is only marked out when the number the culmulative revenue is reached for each product. But product A,B,C,D may all have a different date.
Do you have a Cumulative Revenue Column already?
Could you paste your dataset and desired OUTPUT?
Try this MEASURE.
Date when 1 Million reached = VAR RESULT = TOPN ( 1, FILTER ( ALL ( TableName[ Date ] ), [Cumulative] > 1000000 ), [Cumulative], ASC ) RETURN IF ( SELECTEDVALUE ( TableName[ Date ] ) = RESULT, Result )
You will get
Thanks
This is exactly what I want!
However now I am still having difficulty with the culmulative measure. The one you wrote earlier on does not have a filter on product type or?
Please check the file attached in my earlier post
It works with the slicer for Products
I have worked out the solution you provided me with.
Is there a way to work without the slicers? So that I will be able to have the date for each product. (Because later I need to create a table including product and the date when the sales volumn reaches 1M)
Many thanks!!
Best,
Qianru
No. I dont have one.
I only have a query containing lines of entries of, product type (a,b,c,d....) and time sold (dates) and revenue earned for that sale.
User | Count |
---|---|
91 | |
73 | |
68 | |
63 | |
55 |
User | Count |
---|---|
99 | |
88 | |
73 | |
60 | |
58 |