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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Tonocop2390
New Member

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 calculation should be April's value + 500.  Then for June, I need to take May's value and add 500 and so on.  

Is this possible to do in DAX?  Is using the same values calculated in a column to complete the rest, self referencing.

Thanks for the help.

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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)

3.png

 

 

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.

 

View solution in original post

3 REPLIES 3
Poojara_D12
Super User
Super User

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 

 

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 - Proud to be a Super User
Data Analyst | MSBI Developer | Power BI Consultant
Consider Subscribing my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS
Anonymous
Not applicable

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)

3.png

 

 

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.

 

lbendlin
Super User
Super User

You cannot do self referencing in DAX.  You can do aggregations like SUMX in some scenarios.

 

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.