Can DAX calculate this? Calculate-Product-By Dates (Conceptual)

[Jkaelin]

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.

[v-huizhn-msft · Accepted 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

[Jkaelin]

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!!

[Greg_Deckler]

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.