Add column to shown Previous Day value for each row

[Anonymous]

Hi... I probably tried everything but nothing is working out. Below is my data and I am trying to add two columns.

1. One column to show the previous day's Used Megabytes for each row.

2. Another column to show the percentage change between previous Used Megabytes and Current Date's Used Megabytes.

Computer Name	Disk	Allocated GB	Total Megabytes	Used Megabytes	Reported Date
Server A	C:	74	75868.16	66709.42	01/05/2019 23:00
Server A	C:	74	75868.16	66711.86	02/05/2019 23:00
Server A	C:	74.2	75868.16	66720.98	03/05/2019 23:00
Server A	C:	73.2	75878.4	66740.71	04/05/2019 23:00
Server A	C:	74	75868.16	66728.63	05/05/2019 23:00
Server A	C:	74	75857.92	66733.4	06/05/2019 23:00
Server A	C:	80	75878.4	66755.84	07/05/2019 23:00
Server B	C:	74	75868.16	26687.31	01/05/2019 23:00
Server B	C:	74	75868.16	26687.31	02/05/2019 23:00
Server B	C:	73	75878.4	26687.31	03/05/2019 23:00
Server B	C:	78	75868.16	26687.31	04/05/2019 23:00
Server B	C:	74	75868.16	36690.27	05/05/2019 23:00
Server B	C:	74	75868.16	40703.07	06/05/2019 23:00
Server B	C:	90	75868.16	16626.96	07/05/2019 23:00

[ChandeepChhabra]

Will the previous day be as per the Computer Name?

If yes then please download my solution using Power Query

Hope this helps

thanks

[Mariusz · Accepted solution]

Hi @Anonymous 

Please see the below.

previous day's Used Megabytes = 
VAR rd = Table1[Reported Date]
VAR cn = Table1[Computer Name]
VAR ld = CALCULATE(
    MAX(Table1[Reported Date]),
    Table1,
    Table1[Computer Name] = cn,
    Table1[Reported Date] < rd
)
RETURN 
CALCULATE(
    MAX(Table1[Used Megabytes]),
    Table1,
    Table1[Computer Name] = cn,
    Table1[Reported Date] = ld
)

% change = DIVIDE(Table1[Previous], Table1[Used Megabytes]) -1 

Regards,
Mariusz

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

[Anonymous]

OH MY GOD...! I have been struggling for this since the past 7 hours. Now words to say. Thanks a lot 🙂