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.
Good morning,
I have a data set, but prefer to keep it conceptual, then if possible, I will work on the actual dax formula.
I am attempting to calculate the product of a column by date, but calculated in this manner: January's result * February's result * March's result = [Result I'm after].
I am aware that I can use the calculate the product from Jan thru March, but my data set is stock returns, therefore, the returns have to be calculated by date & in order.
Is this possible with dax? Any tips or ideas??
Thank you & kindly,
James k.
Solved! Go to Solution.
Hi @Jkaelin,
I create the following sample table, the data is from 2013/1/1 to 5015/7/20. I think it is similar with your data set structure.
1. Create a calculate to get Month using the formula:
Month = FORMAT(Stock[date],"MMM") YEAR = YEAR(Stock[date])
2. Please create a measure based on the new table. Create a table visual to display the result. Please see the following screenshot, it shows January's result * February's result * March's result in very year.
Result = CALCULATE(SUM(Stock[price]),FILTER(Stock,Stock[Month]="Jan"))*CALCULATE(SUM(Stock[price]),FILTER(Stock,Stock[Month]="Feb"))*CALCULATE(SUM(Stock[price]),FILTER(Stock,Stock[Month]="Mar"))
Don't hesitate to ask if you have any other issue.
Best Regards,
Angelia
Hi @Jkaelin,
I create the following sample table, the data is from 2013/1/1 to 5015/7/20. I think it is similar with your data set structure.
1. Create a calculate to get Month using the formula:
Month = FORMAT(Stock[date],"MMM") YEAR = YEAR(Stock[date])
2. Please create a measure based on the new table. Create a table visual to display the result. Please see the following screenshot, it shows January's result * February's result * March's result in very year.
Result = CALCULATE(SUM(Stock[price]),FILTER(Stock,Stock[Month]="Jan"))*CALCULATE(SUM(Stock[price]),FILTER(Stock,Stock[Month]="Feb"))*CALCULATE(SUM(Stock[price]),FILTER(Stock,Stock[Month]="Mar"))
Don't hesitate to ask if you have any other issue.
Best Regards,
Angelia
Thanks @v-huizhn-msft
This seems to work well. I spent much time working on this yesterday. Another solution for me was to create a "productx" function and use the date to summarize the results. Both work great & thank you for looking into this for me!!
You could create a SUMMARIZE table grouped by month and then measures off of that table to grab the numbers for Jan, Feb, March and multiply them together. Sample data would provide a more useful response.
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 |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |