The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
94 | |
94 | |
86 | |
69 | |
65 |
User | Count |
---|---|
240 | |
125 | |
122 | |
82 | |
80 |