cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

Time since last timestamp across two tables

Hi

 

Hoping to get some advise / a solution on this one. Hoping its something simple and I am over thinking it.

 

I have one table of "Activity" and one table of "Accounts".

Both tables have a UserID column (User_Key).

 

On the Activity table, there is a Timestamp for the "Activity" and I want to know the time (in days) since that account was last online.

 

I can do a simple calculated column to calculate = today()-[Timestamp)*1 and format as a number to get the days since that timestamp, then group by the result. 

 

This is in the attached model on the Activity Table [Days Since Last Visited].

 

This is where it gets interesting though, not every account is on the "Activity" table, as not every account has been online yet. 

We have 38 unique User_Keys in the Activity Table but there us 55 unique User_keys in the Accounts table where [Is Deleted] is FALSE. 

 

So how would I do either a measure or column to calculate Days since last active and group the results by;

0, <7, <14, <21, 22-40 and 41-90 days.

 

Where any non-active accounts without a logged timestamp would fall into the 41-90 days category/grouping?

If it made it any easier though non-active accounts could equal never used. 

 

Attached an example pbix to show the tables and columns and hopefully give some context to what I'm trying to achieve. 

 

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

 

Thanks,

 

Josh

3 REPLIES 3
Community Support Team
Community Support Team

Re: Time since last timestamp across two tables

HI, @Joshua_Peter

What is your expected output? since you had created the column [Days Since Last Visited] as a group,

then use column calculate = today()-[Timestamp)*1  with it get your result.

 

So what is your other expected output? could you please explain the expected output with the sample data? 

 

Best Regards,

Lin

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

Re: Time since last timestamp across two tables

Hi 

 

The expected output is to group the days since last online by; 

0, <7, <14, <21, 22-40 and 41-90 days

 

but using all the accounts from the "Accounts" table and including null values on the grouping. 

 

So a bar chart with the below on the Y axis and then Distinct Account User_Key count as the values to total 55 Accounts but I also want the null values so we can see 0 accounts have been online 0 days ago. 

 

0,

<7,

<14,

<21,

22-40

41-90 days

 

I did later manage to get all 55 accounts in the equation using 

Last Pageview = MAX([Timestamp (Visitor Time)]) on the Activity Table, 

 

Then Calculated column on the User table for IF(today - [last pageview]*1=7, "7",

IF... and so on... 

 

So anyone without any activity would have a huge last online number of days ago and automatically be in the last group. 

 

So now the main issue I'm having is getting the null values to show. 

 

I was looking at some other materials and that suggested using the calculated column to just do Today()-Last pageview*1 to get the numeric value and then use the visual group by feature to see the groupings but i have been struggling with it. 

 

Thanks. 

 

 

Community Support Team
Community Support Team

Re: Time since last timestamp across two tables

hi, @Joshua_Peter

Is your expected output is a group value for each User_Key in Accounts?

User_Key 0 <7 <14 <21 22-40 41-90 days
986   5 3 2 1  
987     2 10    
988         11 5
989   1   2    
         

 

 

If so you could refer to this post:

https://community.powerbi.com/t5/Desktop/Group-Measure-values-on-rows/m-p/514475#M240454

 

And So now the main issue I'm having is getting the null values to show

you could add a "+ 0"  at the end of the formula then null values will be 0

 

 

Best Regards,

Lin

 

 

 

 

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