Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
ForzaMami
Regular Visitor

one top row data

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 ?

ForzaMami_0-1650617664529.png

Thanks

11 REPLIES 11
ForzaMami
Regular Visitor

did i want something so hard 🙂

@ForzaMami 
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)

tamerj1
Super User
Super User

Hi @ForzaMami 

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,

 

Dont work. If you want to get the 1st and 2nd months retrospectively, it doesn't work either.

 

 

ForzaMami_0-1650867028547.png

 

@ForzaMami 

Can you please right the expected results with explanation in this same screenshot?

Hi ,

 

ForzaMami_1-1650886763975.png

 

 

Exponential_1 =
([Input2Values]*[Measure Last Period]) + ([Input2Values] * [Measure Last Period](Wrong) )
 
20200301 = (0,5*633.973) + ( 0,5*4.076.361 ) = 2.355.167 must
20200401 = (0,5*766.859) + ( 0,5*2.355.167 )= 1.561.013 must
 

 

 

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.

How to upload PBI in Community

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi ,

I tried to make an example. I just want Exponential Smoothing column


PBX
https://easyupload.io/gouz99

Data and Sample
https://easyupload.io/u81aea

 

ForzaMami_0-1650960766003.png

 

Thank You

 

@ForzaMami 
Would you please upload the files as the link is already expired. 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...

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @ForzaMami 
It is complicated but actually there might be a solution. Following is a silution based on a calculated column. Not sure if this is what you are looking for. https://we.tl/t-k7c9RgpgGw

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

1.png

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.