Calculations within the same column and related column

[NabihaF]

Hello, 

I have data in this format:

I want to calculate the difference between the date linked to first_open and the date linked to app_remove.

How can i do that? is it possible?

[Whitewater100]

Hi:

I assume you want that difference based on the user id field?

Can you send example data with some ID;s that have user_action with both dates? It will be easier to work on data example.

Thanks..

[NabihaF]

Hi, here you go:

user_pseudo_id	user_action	event_date
00501ccd007a26fe4eb0e5ba77d34ade	app_remove	03/05/2022
00ad8194133c426cffb6ec2aa3deb745	app_remove	03/05/2022
009fab1c9b03791fc35fb8d9dac5a6fa	app_remove	29/04/2022
00f8357f1414565d7fe4d9765c3f3f83	app_remove	29/04/2022
01cd6bc125b85f286efd0ff3350ddbea	app_remove	30/04/2022
000de56dc6fc2f6acd858670eaf37750	app_remove	30/04/2022
01642376dc47a6ae2c6483fc716bcb8e	app_remove	30/04/2022
0162782356cacc87d12a750eb0eeed08	app_remove	30/04/2022
018739eeb0d82786062ec4e67c4f68b1	app_remove	01/05/2022
02239ab06d6e2329ceffb918bae34475	app_remove	01/05/2022
00ad8194133c426cffb6ec2aa3deb745	first_open	03/05/2022
02239ab06d6e2329ceffb918bae34475	first_open	01/05/2022
018739eeb0d82786062ec4e67c4f68b1	first_open	01/05/2022
009fab1c9b03791fc35fb8d9dac5a6fa	first_open	06/04/2022
0162782356cacc87d12a750eb0eeed08	first_open	21/04/2022
01642376dc47a6ae2c6483fc716bcb8e	first_open	24/04/2022

[Whitewater100 · Accepted solution]

Hi:

Please see file for calc column. I hope this is a good solution for you!

https://drive.google.com/file/d/1bwbHewq0a3b0qU1oK9ntS5CK-yFAUSxL/view?usp=sharing 

[NabihaF]

Hi,

Thank you so much!.

This looks good, but i need to try it out at my end. Just a quick question though, can i still insert a calculated column even though the data is coming in via direct query?

[NabihaF]

Yes, unfortunately it is not working on direct query...

Is there any other way to make this query?

[Whitewater100]

Hi:

I did not relaize DQ. I know it is more limited. I've tried another way which is on the attached file. If you could do import, it's easier for modeling. I will paste link on limitations of DQ.

https://drive.google.com/file/d/1bwbHewq0a3b0qU1oK9ntS5CK-yFAUSxL/view?usp=sharing 

Using DirectQuery in Power BI - Power BI | Microsoft Docs

I hope you can mark original question as solved.. Thanks

[NabihaF]

Will it be possible for you to please explain this soltuion. I see youve created two new tables?App remove and app open?

[Whitewater100]

Hi:

I created the two tables as a way to use LOOKUPVALUE to finangle open & close date on same line. Then it's easier to subtract the date values.

I'm not using DQ (only) very much and understand it is good to use if you have massive data or you need updated reslts quite often. This was simply another approach I tried to get you the answer.