how to calculate Y(n)=X(n)+Y(n-1) in DAX?

[Anonymous]

Hello! I have a measure X, and also a calculated column N. 

N    X

1    0.9

2    0.8

3     0.7

4    0.8

.....

I need to calculate a measure Y, so that Y(n)=X(n)+Y(n-1) 

so that 
N  X   Y

1 0.9   0.9
2 0.8   1.7

3  0.7   2.4

.....

In this case , N is a calculated column , X is a measure I calculated, how to calculate Y?  Thank you in advance!

[Mikelytics · Accepted solution]

Hi DuoHappy,

I did it with a date tables as an example

in my case N=date of date table (column)

X= a Measure which counts the days 

Y = the measure which calculates the running total based on the date

Y = 
CALCULATE(
    [X (Count of Days)],
    FILTER(ALL('Dim Date'),
        'Dim Date'[Date] <= MAX('Dim Date'[Date])
    )
)

[Mikelytics · Accepted solution]

Did you try this?

Y = 
CALCULATE(
    [X],
    FILTER(ALL('Table where N is stored'),
        'Table where N is stored'[N] <= MAX('Table where N is stored'[N])
    )
)

[v-yalanwu-msft · Accepted solution]

Hi, @Anonymous ;

You could try it.

Measure = SUMX(FILTER(ALL('Table'),[No]<=MAX('Table'[No])),[X])

The final show:

If the above one can't help you get the desired result, please provide a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Mikelytics · Accepted solution]

Hi DuoHappy,

I did it with a date tables as an example

in my case N=date of date table (column)

X= a Measure which counts the days 

Y = the measure which calculates the running total based on the date

Y = 
CALCULATE(
    [X (Count of Days)],
    FILTER(ALL('Dim Date'),
        'Dim Date'[Date] <= MAX('Dim Date'[Date])
    )
)

[Anonymous]

In your case, NO. is from dim date, but in my case No. is a calculated column from Fact table, which has multiple values.

[Mikelytics]

Hi DuoHappy,

It doesn't matter. It also works with duplicates thanks to the MAX() function.

Best regards

Michael

[Mikelytics · Accepted solution]

Did you try this?

Y = 
CALCULATE(
    [X],
    FILTER(ALL('Table where N is stored'),
        'Table where N is stored'[N] <= MAX('Table where N is stored'[N])
    )
)

[Anonymous]

sorry, it is not working in my case

[Mikelytics]

ah okay,

is there a multiplication in your x measure going? If yes you have to sorround my solutuion with a SUMX

Y = 
SUMX(
VALUES('Table of N'[N]),
CALCULATE(
    [X],
    FILTER(ALL('Table where N is stored'),
        'Table where N is stored'[N] <= MAX('Table where N is stored'[N])
    )
)
)

[Anonymous]

Hello! I thought I can not using your suggestion to get the right answer. This might because in my calculation of X, I also used all function in the formula. and now Y if using all function again, which caused problem so that this value is not correct

[Anonymous]

Hello! Thanks ! I am thinking Y= 

CALCULATE(
    sum([X]),
    FILTER(ALL('Table where N is stored'),
        'Table where N is stored'[N] <= MAX('Table where N is stored'[N])
    )
)

but it is wrong.

[Jihwan_Kim]

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

Expected result measure: =
[Sales measure:]
    + CALCULATE ( [Sales measure:], Data[Index CC] <= MAX ( Data[Index CC] ) - 1 )

[Anonymous]

Hi, using you syntax can not get the value you showed, please see. Could you please give more guidance?

[Jihwan_Kim]

Hi,

Please share your sample pbix file's link here, and then I can try to look into it.

Thanks.

[VijayP]

@Anonymous 

in the above case is actually a multiplication of column wiht measure is that right?
then in the data view along with N , add another column with  X = X measure , it gives you the value of measure as a column in the table

Then you can use sumx(table, n*x) will give youthe value as requried!