Time since last timestamp across two tables

[Anonymous]

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

[v-lili6-msft]

HI, @Anonymous

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

[Anonymous]

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. 

[v-lili6-msft]

hi, @Anonymous

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