Can I replicate Product function from excel in DAX?

[VTB]

Trying to get Dax to do a Product array calculation, perhaps there is a better way too. 

if i have a table with below:

Date	Spread
6/19/2019	0.007414
6/20/2019	-0.00574
6/21/2019	-0.0049
6/24/2019	0.0095
6/25/2019	0.002036
6/26/2019	-0.01948
6/27/2019	-0.00706
6/28/2019	-0.00784

and I am trying to get to {Product(1 + SpreadColumn row 1:8)}, i should get a result of 97.39165. 
what function would get me there in Dax? also, if it's able to be filtered by Date ranges as well which would be great! (which would drive the amount of rows the product array considers in the calculation. )

Thanks, 

[Nathaniel_C · Accepted solution]

Hi @VTB ,

Yes there is product in Dax, and added another col to the table as PRODUCT only takes a column, with a slicer for the date.

m1 = PRODUCT(Prod[plus 1])

Be aware that when you go to format your table, the number of decimal places is grayed out, but just type the number into the box.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

[VTB]

hmmm that's weird, when I do a product function with plus 1 column, I just end up getting a clone of itself in the new measure. Instead of it calculating it over the date range. Any ideas why thats happening? I disabled all "summarize by" on columns just to be sure. 

whoops - figured it out, filtered stuff incorrectly, now that it's fixed, your approach works like a charm!

Also - is there a way to anchor a product function to do last 3 days/months for each date row? Like running last 3 day trailing Product calc type of thing that would be dynamically updated. I tried filter functions with date variables, but didnt get it to work properly. (instead of using a slider always, if i wanted to have static table columns showing up instead)

thanks, 

[Nathaniel_C]

Hi @VTB ,

Look at this solutionTrailing date or google trailing date dax.  Let me know what you come up or which video you watched.  I need to get that one also.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

[VTB]

just watched this:

https://www.youtube.com/watch?v=duMSovyosXE

then replaced with ProductX instead of Sum to iterate over rows - and it worked! This has been a good night!

[Nathaniel_C]

@VTB ,

Here is my pbix for you to play with. Product

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel