Calculate Change by previous row value

[Nourhassan]

Hello,

I need to calculate the load change hourly, so how the load is changed from time to time per percentage like the table below. 

I have a table with a TIME (From 0 to 23) Column and Load value. Based on that i need to calculate the change in percentage. The Load Change in Time 0 should be here empty like shown in the above screenshot. The load change in Time 1:00 was calculated here as  follows: [(1963.935 - 1986.167) / 1963.935]. 

Thank you in advance.

[Nourhassan]

Hello, 

Is it possible to add this column to another table without any relationship?

[lbendlin]

I don't understand what you are trying to accomplish. Please elaborate.

[Nourhassan]

I have these two tables below:

I have a Report with a new Calculation, named "Expected Demand" which multiply the "Load Change" from Table DailyLoad with "Switch_MW"  from Table ENS. Both tables are not in relationship. I don't know how to handle this. 

Expected Demand (MW)

Switch MW*Load change %

The Load change in the Calculation "Expected Demand" has to be chosen from the correct timezone in Table dailyload.

It is more clear now?

[Nourhassan]

The new column has to be added to ENS Table.

[lbendlin]

Either you find a relationship (a column that matches between the tables) or you use LOOKUPVALUE() if a relationship cannot be determined.

[Nourhassan]

I don't have any column that can build a relationship between the tables, so I need to use LOOKUPVALUE(). Can you help me with how to use it in this case?

[lbendlin]

I have no expertise in using LOOKUPVALUE. I try to avoid using that function, and prefer to have a solid data model to work with.

[Nourhassan]

To know to which date the data is from. Maybe, later the dataset will be extended with other dates too. Then I can compare both dates with each other. 

[lbendlin]

I added the date column.  Not sure what added value it provides. See attached.

[Nourhassan]

Thank you for your help. I really dont know what the problem is. This is my table below:

[lbendlin]

Is your table called 'Table' or something else?

[Nourhassan]

Something else, but I have been changed it also in the measure. 

[Nourhassan]

hello @lbendlin,

is that helpful? Or do you need it as excel file?

R_TIME	LOAD
0	795
1	724
2	697
3	677
4	667
5	658
6	734
7	692
8	707
9	728
10	756
11	766
12	764
13	730
14	725
15	780
16	779
17	781
18	881
19	860
20	838
21	837
22	837
23	811

[lbendlin]

Thank you for providing sample data.  Something like this?

see attached

[Nourhassan]

I think you have made a mistake with the number to divide. The dividend should be the first number of Subtraction (Minuend).

[lbendlin]

I don't think so.  Value growth computations generally follow the pattern 

(New Value minus Old Value) divided by Absolute of Old value

[Nourhassan]

Also, when I put this measure in my table, then I got an empty column without error. 

Can someone help me please? I don't know why the column is blank

[lbendlin]

My proposed solution is based on the sample data you provided.  If the sample data does not cover the issue fully then please provide more descriptive sample data.

[Nourhassan]

Unfortunately, I don't have permission to upload the .pbix file here. 

Kindly find below a table with more data. 

R_DATE	R_TIME	LOAD
12/01/2021	0	795
12/01/2021	1	724
12/01/2021	2	697
12/01/2021	3	677
12/01/2021	4	667
12/01/2021	5	658
12/01/2021	6	734
12/01/2021	7	692
12/01/2021	8	707
12/01/2021	9	728
12/01/2021	10	756
12/01/2021	11	766
12/01/2021	12	764
12/01/2021	13	730
12/01/2021	14	725
12/01/2021	15	780
12/01/2021	16	779
12/01/2021	17	781
12/01/2021	18	881
12/01/2021	19	860
12/01/2021	20	838
12/01/2021	21	837
12/01/2021	22	837
12/01/2021	23	811

The problem only occurs when the date column is added. I think the problem is that it is the same day in each row.

[lbendlin]

Your sample data is not fully covering your issue.  Provide at least two days'  worth of hourly snapshots.