Forum Discussion
Can I replicate Product function from excel in DAX?
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,
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
5 Replies
- Nathaniel_C
Community Champion
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.
Nathanielhmmm 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
