Forum Discussion

Tonocop2390's avatar
Tonocop2390
New Member
1 year ago
Solved

How to create a column that completes itself based on values from the column itself?

For example I have a table that has actuals from January to April, and then for the rest of the year, I have to calculate based on the previous value. Let's say April has 1000, so for May my calcula...
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Tonocop2390 ,

    Please try the code below.

     

    Column2 = 
Var _Month=
CALCULATE(
    MAX('Table'[Month]),
    FILTER('Table',[Value]<>BLANK())
    )
Var _Sum=
CALCULATE(
    SUM('Table'[Column]),
    FILTER('Table',[Month]<=EARLIER('Table'[Month])&&[Month]>=_Month)
    )
RETURN
IF([Value]<>BLANK(),[Value],_Sum)

     

     

    Best regards,

    Lucy Chen

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

     

Poojara_D12's avatar
Poojara_D12
Icon for Super User rankSuper User
1 year ago

Hi Tonocop2390 

Yes, you can achieve this in DAX by creating a calculated column using a pattern that references previous rows. However, DAX doesn't allow true self-referencing in calculated columns directly. Instead, we use functions like EARLIER or VAR along with row context to simulate this behavior.

 

Create a Calculated Column

DAX:

 

Calculated Column =
VAR CurrentMonth = Table[Month]
VAR PreviousMonthValue =
    CALCULATE(
        MAX(Table[Calculated Column]),
        FILTER(
            Table,
            Table[Month] = FORMAT(DATEADD(DATEVALUE("1 " & CurrentMonth), -1, MONTH), "mmmm")
        )
    )
RETURN
IF(
    ISBLANK(Table[Actuals]),
    PreviousMonthValue + 500,
    Table[Actuals]
)

 

 

Also, 

If your table isn't sorted by months, ensure to use a date field or sort it correctly for accurate calculations.

DAX calculated columns are evaluated row by row, so they cannot dynamically reference themselves. The FILTER and CALCULATE pattern simulates this behavior.

 

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
Consider Subscribing my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS