DAX: Multiply by the product of the previous record

[vdburg]

Hi,

I need to get the product of the previous record multiplied by current percentage with DAX.

The dax formula should return the value in the 'Calculation' column. The 'Formula' column shows how the calculation is done in Excel.

	B	C	D	E
2	Periods	Percentage	Calculation	Formula
3	1	0,99	0,990	=C3
4	2	0,98	0,970	=+C4*D3
5	3	0,99	0,960	=+C5*D4

I think that the productx function should be used to do this but I can´t find how. 

This has to be a pretty common calculation, I would appreciate if anyone who dealt with this too could give me a hint.

Thanks,

Robert

[Mariusz]

Hi @vdburg 

The below will address your issues.

Product x = 
VAR _AllPreviousPeriods = 
FILTER(
    ALL( YourTable ),
    YourTable[Periods] <= MAX( YourTable[Periods] ) 
)
RETURN PRODUCTX( _AllPreviousPeriods, YourTable[Percentage] )

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

[vdburg]

Hi @Mariusz ,

It did work, however if I add a dimension like 'year' it fails. Do you know of any workaround?

[Mariusz]

Hi @vdburg 

You need to replace 'Table1' with Calendar Table / Date Dimension

Like below.

Product x = 
VAR _AllPreviousPeriods = 
FILTER(
    ALL( Calendar ),
    Calendar[Date] <= MAX( Calendar[Date] ) 
)
RETURN PRODUCTX( _AllPreviousPeriods, YourTable[Percentage] )

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

[quickcreator]

How would you do this is the % return were a measure from two separate tables sliced down to get the % return? Then continuously multiply the %returns down each row to get a cummulative %return?

[vdburg]

Hi @Mariusz ,

I think that unfortunately this approach won´t work because I used a simple example, but I will need to do the calculation with many more dimensions and perform the calculation grouping and degrouping over dimensions.

For example I got this table detailing percentage churn by year and region.

But I will also need to agregate the years together:

Many thanks

[Mariusz]

Hi @vdburg 

Looking at your example, how would you wont the product to xcalculated, over year and month but with in the region?

Many Thanks

Mariusz 

[vdburg · Accepted solution]

Hi,

I have been testing several ways and this does it:

PRODUCTX( filter(ALL(Table1[Periods]) ; Table1[Periods]<= MIN(Table1[Periods])) ; Table1[Percentage_] )

@Mariusz  thanks for you help 

best regards

[Mariusz]

HI @vdburg 

No problem, I'm glad you worked it out

Thanks 
Mariusz