Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |