topic Re: Calculation by Row by Year in DAX Commands and Tips

Calculation by Row by Year

Fantmas — Tue, 03 Jan 2023 10:33:29 GMT

Hi all,

I am trying to create a measure to Dynamically calculate by row a value through year :

In my first table I have the value a & b
a is a decimal number
b is a integer

In a another table I have my value:

t is the year starting from 1 and go up to 6

the year can dynamically change and I am stuck in my DAx formula as I am using CALCULATE and SUM

Supply = CALCULATE( CALCULATE(SUM(a) * POWER((1 - SUM(b),t))

In excel the formula is as Follow :

a * (1 - b ) ^ t

Re: Calculation by Row by Year

MatthRichardsUK — Tue, 03 Jan 2023 11:25:53 GMT

To replicate this formula in DAX, you can try the following:

Supply =
CALCULATE(
SUM(a) * POWER(1 - SUM(b), t),
ALLSELECTED(table)
)

This will calculate the value of Supply using the values of a and b in the current context, and raise the result of 1 - SUM(b) to the power of t. The ALLSELECTED function is used to remove any filters on table, so that the full range of a and b values is used in the calculation.

If you want to use a specific year value for t instead of a dynamic value, you can replace the t column reference with a fixed value or another measure. For example:

Supply =
CALCULATE(
SUM(a) * POWER(1 - SUM(b), 3),
ALLSELECTED(table)
)

This would calculate the value of Supply using the values of a and b in the current context, and raise the result of 1 - SUM(b) to the power of 3.

I hope this helps! Let me know if you have any further questions.

Re: Calculation by Row by Year

tamerj1 — Tue, 03 Jan 2023 12:37:09 GMT

Hi Fantmas
Please try

Supply = VAR t = SELECTEDVALUE ( Table2[Year] ) RETURN SUMX ( Table1, VAR a = Table1[ValueA] VAR b = Table1[ValueB] RETURN a * ( 1 - b ) ^ t )

Re: Calculation by Row by Year

Fantmas — Tue, 03 Jan 2023 12:48:45 GMT

Hi Matth,

Thank you for your answer, I tried your solution but it is not working, I should find a figures around 600 for my test but I find a figures around 2M,

My assumption it is POWER function is done after the sum function that create the issue