It's a little hard to explain, I need to use the same formula in a single dax formula, the one in the top row. I'm trying to power the "Exponential Smoothing" column in the excel below. Is something like this possible ?
My dear, I think the thing you somehow missed is that DAX does not support recursive calculations. Meaning that you cannot by any means refer to the previous raw of the same column under evaluation. In some cases, the same results can be obtained using different approach.
In this community there are people who are willing to spend time and effort to help others sometimes having no idea about their data expecting some cooperation to clarify it out and help them find a solution.
My question to you is there any way we can calculate the values depending only on the Status column? Can you transform your equation to a one where all variables belong to the status column only? (no matter how complex this equation would be)
you may try
Exponential Smoothing = VAR CurrentYearMonth = MAX ( Table[Year_Month] ) VAR PreviousStatus1 = CALCULATE ( [Status], Table[Year_Month] = CurrentYearMonth - 100 ) VAR PreviousStatus2 = CALCULATE ( [Status], Table[Year_Month] = CurrentYearMonth - 200 ) RETURN IF ( NOT ISBLANK ( PreviousStatus1 ) && NOT ISBLANK ( PreviousStatus2 ), IF ( ISBLANK ( PreviousStatus1 ), PreviousStatus2, 0.5 * PreviousStatus1 + 0.5 * PreviousStatus2 ) )
Hi @ForzaMami ,
You can create a measure as below to get it:
Exponential_1 = VAR _curym = SELECTEDVALUE ( 'Table'[Year_Month] ) VAR _selalpha = SELECTEDVALUE ( 'Table'[Alpha] ) RETURN _selalpha * SUMX ( FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Year_Month] <= _curym ), [Measure Last Period] )
If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.
Hi @ForzaMami ，
According to the information inside the latest reply you gave, the result of the current item is calculated based on the result after the previous item is calculated. And as @tamerj1 said, it involves recursive calculation, I'm afraid it's difficult to achieve what you need with DAX...
Exponential = VAR a = 0.5 VAR CurrentDate = Sales[Year_Month] VAR FirstDateEver = CALCULATE ( MIN ( Sales[Year_Month] ), REMOVEFILTERS ( ) ) VAR FirstAmount = MINX ( FILTER ( Sales, Sales[Year_Month] = FirstDateEver ), Sales[Sales] ) VAR T1 = FILTER ( Sales, Sales[Year_Month] < CurrentDate ) VAR PowerNum1 = COUNTROWS ( T1 ) - 1 VAR Value1 = IF ( PowerNum1 >= 0, FirstAmount * POWER ( a, PowerNum1 ) ) VAR Value2 = SUMX ( T1, VAR PowerNum2 = COUNTROWS ( FILTER ( T1, Sales[Year_Month] >= EARLIER ( Sales[Year_Month] ) ) ) - 1 VAR Amount = MAXX ( FILTER ( T1, Sales[Year_Month] = EARLIER ( Sales[Year_Month], 1 ) + 100 ), Sales[Sales] ) RETURN POWER ( a, PowerNum2 ) * Amount ) RETURN Value1 + Value2
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.
130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.