Insert a new row in PowerBI Table based on calculation

[ArunaM]

I have a series of data and need to generate forecast based on the max date and slope for next 1 year using the formula y=mx+b. After the current date it has to insert a new row in the table based on the formula calculation. Can someone help me how to do it or run loops in power bi.

and the expected output was like this

Account_Name	APP_FULL_NAME	MAX_DATE	INDEX
Dev	a	8/11/2020	2
Dev	a	8/12/2020	3
Dev	a	8/11/2021	365

[v-lili6-msft]

hi  @ArunaM 

For your case, you could use DAX to create a new table

or create measure and show it in the table viusal.

What is your logic of your expected output(y=mx+b)? 

Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Regards,

Lin

[mahoneypat]

If you really don't need the rows actually added to the model, you could do this with DAX (i.e., calculate future values based on past values).  You can first try to use the Analytics pane of the visual to add forecasting.  If that meets your need, great.

If not, it is silly, but it is much harder than it needs to be to calculate slope and intercept with DAX. 

Please see this post - https://xxlbi.com/blog/simple-linear-regression-in-dax/

To do it w/o adding actuals rows you would have a Date table that contains your future dates, make a measure that makes a virtual table of your "past" values (x and y), calculate slope and intercept for those values, find the # of days in the future your "current" date is (i.e., your new X value) and plug it into the y=mx+b calculation to plot your result.  It's doable but harder than it should be.

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[ArunaM]

How can I create future dates based on current or previous date, this is where I got stuck.

[v-lili6-msft]

hi @ArunaM 

Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

Regards,

Lin

[lbendlin]

You cannot do that with Power BI tables.  You either will have to use (non-persistent) table variables, or you need to move this request to Power Query.

[ArunaM]

How can i do it in Power Query? Thanks!