topic Re: Get difference between values in a column preceding each other by date in DAX Commands and Tips

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

Anonymous — Sun, 11 Apr 2021 13:09:21 GMT

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.

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

Jihwan_Kim — Sun, 11 Apr 2021 13:37:53 GMT

Hi, Anonymous

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.

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

Anonymous — Sun, 11 Apr 2021 15:16:51 GMT

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.

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

Jihwan_Kim — Sun, 11 Apr 2021 15:26:01 GMT

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.

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

Anonymous — Sun, 11 Apr 2021 15:33:28 GMT

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.

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

Jihwan_Kim — Sun, 11 Apr 2021 15:56:26 GMT

Hi, Anonymous

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.

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

Anonymous — Sun, 11 Apr 2021 16:11:17 GMT

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.

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

daxer-almighty — Sun, 11 Apr 2021 23:10:37 GMT

Anonymous

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).

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

Jihwan_Kim — Mon, 12 Apr 2021 03:06:15 GMT

Hi, Anonymous

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?

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

Anonymous — Sun, 25 Apr 2021 10:08:22 GMT

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.