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.

     

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

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-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523