Get difference between values in a column preceding each other by date

[Mamr]

Hi,

I have a scenario that i want to apply.

I want to create a new column that holds the difference between each 2 preceding values based on date.

for example :-

Date                     Value              Difference

15/5/2021              125                    0

19/5/2021              127                    2

20/5/2021              134                    7

23/5/2021              135                    1

and so on ..

Please, How can i achieve this ? i want to plot it on a line chart also based on the calculated column.

Thank you in advance.

[daxer-almighty]

@Mamr 

This calculation belong to Power Query, not DAX. Just to let you know. If your table is big (think: fact table), the calculation given by @Jihwan_Kim will be way too slow due to context transition(s).

[Jihwan_Kim]

Hi, @Mamr 

Please check the below picture and the sample pbix file's link down below, whether it is what you are looking for.

I created a new column, and it is not a calculated measure.

https://www.dropbox.com/s/rqys8dykshli41w/mamr.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

[Mamr]

Hello @Jihwan_Kim 

Thank you very much for your responsive reply.

I have applied the query you have mentioned, But i get some strange values, that are presented in the attached screenshot.

I make a filter on specific "Account" column to "Al Futtiam Comme" to validate the results on the line chart.

[Jihwan_Kim]

Hi,

Thank you for your feedback.

The reason is that the table structure is different between the sample shown in your first question and the screenshot. 

If it is OK with you, please share your sample pbix file's link, then I can try to come up with a more accurate solution.

Thanks.

[Mamr]

Hello @Jihwan_Kim 

Thank you very much.

This is the link for my pbix file.

https://drive.google.com/file/d/1GzdNxzjOXTco0DaBFf_S7Hvl5CKh7ngP/view?usp=sharing

Thank you in advance.

[Jihwan_Kim]

Hi, @Mamr 

I can download your pbix file, but I cannot connect to your source, and cannot see your data properly.

I think you should make a new sample file. 

For instance, copy the table, and create a new pbix file, paste the table into the new pbix file, then delete some of the importance information, then share the new pbix file's link.

[Mamr]

Hi @Jihwan_Kim 

You are right.

Here is the sample pbix file link.

https://drive.google.com/file/d/14lYQe8lJqftpCC4edo7Nz8jb5gNt9Yly/view?usp=sharing

Thank you in advance.

[Jihwan_Kim · Accepted solution]

Hi, @Mamr 

I have one question.

There were two lines found that indicate the same information except the Record.

For instance,

2020 Oct 1st -> CAE-HQ 

Two records are found (6625 and 8184). May I ask how to differentiate these? Or, can I just sum up?

[Mamr]

Hello @Jihwan_Kim 

I really thank you for your responsive reply.

I have solved the issue by getting also the specific time with the date, so there is no duplication in the data structure as you said.

Great respect for your support.