DAX to calculate a value using the previous calculated value on the same column

[Anonymous]

Hello!

I have a date table like the one above where in one column I have target values and in another column I have actual values and, since it's actual values, I don't have values for future days.

	Date	Target	Actual
	01/01/2020	2.669	3.113
	02/01/2020	2.700	3.123
	03/01/2020	2.761	3.194
	04/01/2020	2.790	3.227
	05/01/2020	2.790	3.211
	06/01/2020	2.825	3.252
	07/01/2020	2.878	3.313
Today	08/01/2020	2.941	3.385
	09/01/2020	2.981	0
	10/01/2020	3.038	0
	11/01/2020	3.079	0
	12/01/2020	3.079	0
	13/01/2020	3.125	0

What I need to do is, create a line chart with the curve of target vs actual, but since I don't have values for future days I need to estimate these values. I have this on Excel and it's works fine so my intention was to use the same logic on Power BI, but here's where the problem begins.

The estimate logic that I use on Excel is the following: (Today Target / Yesterday Target) * Yesterday Actual. Basically I apply the predicted growth on the actual value to create the curve.

	Date	Target	Actual	Calculated Target Growth (Today Target / Yesterday Target)	Calculated Actual (Calculated Target Growth * Yesterday Actual)
	01/01/2020	2.669	3.113	0,000000	3.113
	02/01/2020	2.700	3.123	1,011798	3.123
	03/01/2020	2.761	3.194	1,022613	3.194
	04/01/2020	2.790	3.227	1,010470	3.227
	05/01/2020	2.790	3.211	1,000000	3.211
	06/01/2020	2.825	3.252	1,012712	3.252
	07/01/2020	2.878	3.313	1,018621	3.313
Today	08/01/2020	2.941	3.385	1,021842	3.385
	09/01/2020	2.981	0	1,013750	3.431
	10/01/2020	3.038	0	1,019011	3.497
	11/01/2020	3.079	0	1,013481	3.544
	12/01/2020	3.079	0	1,000000	3.544
	13/01/2020	3.125	0	1,014848	3.596

The problem is: on Excel I can use the value of the previous cell on the same column and make a calculation with it, but since Power BI consider only the entire column instead of cells I'm struggling on how to do this on it since I need to get the previous calculated value of the same column to calculate again on the same column.

Oh, and the table comes from a database wich has a lot of columns, so I need it resumed (the sum of values).

Can someone help me with this? Or at least tell me if it's possible. I already wasted 3 days trying to do this. I'm an expert on Excel, but in DAX I'm a novice.

Thank you!

[V-lianl-msft]

Hi @Anonymous ,

Based on your description, if using line chart to show forecasting can meet your needs, you can refer to these blog:

https://databear.com/forecasting-in-power-bi/ 

https://www.mssqltips.com/sqlservertip/5085/data-forecasting-and-analytics-with-power-bi-desktop/ 

(Note: This link contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.)

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

[amitchandak]

Try following, But you need to have a calendar table

yesterday Target = calculate(sum(Target), dateadd('Date'[date],-1,DAY))
yesterday Actual = calculate(sum(actual), dateadd('Date'[date],-1,DAY))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s.

Refer
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[Anonymous]

Hello @amitchandak, thank you for your reply.

I created the calendar table and used the formulas you posted. But the main issue persists: in the today Actual I need to apply the percentage (calculated_target_growth) to the previous calculated today Actual.

Like I said, I don't know if it's possible 'cause the way Power BI treats its data, as far as I know it's not possible to use the result of a measure/column in the same measure/column, but I'd like to know if it's possible to do it in another way.

Let's take the table below (which I created in the calendar table with the formulas you posted). In the day 09/01/2020 there's no actual so I need to estimate it. So it will be the actual value of "yesterday" 08/01/2020 (3123) * growth (1,03) which is 3216,69. Now when repeating the proccess to 10/01/2020, the start value will not be the same 3123, it will be 3216,69 which I have just calculated, so it will be 3216,69 * 1,03, which is 3313,1907 and so on. It's like a cumulative but using the previous calculated value.

If that's what you meant in your reply, please forgive me.

[amitchandak · Accepted solution]

@Anonymous 

Please find solution at

https://www.dropbox.com/s/nkp1163ih10j7i7/target_actual.pbix?dl=0

There few columns created for the same.