Product of rows in DAX

[DR2022]

Hi! It is possible to calculate the product of the values in the same way that the accumulate sum is calculated? Below is sample data in a spreadsheet.

 

[OwenAuger]

Hi @DR2022 

Yes, you can use the PRODUCTX function to return the product of (1+Rate) for each row, with an appropriate filter applied.

Alternatively, you could store (1 + Rate) in a column and use PRODUCT.

To give a simple example, assume you have a table similar to your example with columns Data & Rate (but with Rate stored as a decimal)

Data	Rate (stored as decimal, formatted as percentage)
12/01/2021	1.63%
01/01/2022	1.01%
02/01/2022	0.54%
03/01/2022	0.73%

Then you could write a measure like this:

Product = 
CALCULATE (
    PRODUCTX ( MyTable, 1 + MyTable[Rate] ),
    -- Ideally use Date table for below filters
    ALL ( MyTable ),
    MyTable[Data] <= MAX ( MyTable[Data] )
)

Ideally, you should have a Date dimension table and apply any date filters to that table, but this illustrates the idea.

Regards,

Owen

[DR2022]

Sorry for the wait in responding. It worked, but the column would need to be inverse. of the last date to first date. I did well in create a column for the accumulated sum, but not with this one. This is a table for correction of monetary values.

[OwenAuger]

No worries, I was assuming you were writing a measure, but for a calculated column I would write something like this:

Product = 
VAR CurrentDate =
    MyTable[Data]
RETURN
    CALCULATE (
        PRODUCTX ( MyTable, 1 + MyTable[Rate] ),
        ALL ( MyTable ),
        MyTable[Data] <= CurrentDate
    )

It may be preferable to create this column using similar logic in the data source or Power Query.

Regards,

Owen

[DR2022 · Accepted solution]

Now it worked for my case. Thank you so much for sharing your knowledge!