Time in days since last timestamp across two tables - grouped by specified ranges

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

I have an example pbix to show the tables and columns and hopefully give some context to what I'm trying to achieve if that would help. Tried to add a link 3 times and the post appears to get blocked because of it, sure I have seen it in the past though.

Thanks,

Josh

[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-jiascu-msft]

Hi @Anonymous,

It seems you already have all the items. You just need to assemble them. What's the desired result? Is it this one in the image? Please also download the demo from the attachment.

1. Create a new table.

2. Rebuild the relationships.

3. Create a measure.

Measure =
DISTINCTCOUNT ( Accounts[User_Key] ) + DISTINCTCOUNT ( Activity[User_Key] )

 

Best Regards,
Dale

[Anonymous]

Hi, 

Thanks for the bpix for review; 

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. Then also included a Is Deleted = False filter so we only see 55 accounts and not any of the deleted accounts. 

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-jiascu-msft]

Hi @Anonymous,

I'm afraid I don't follow you much. The null values show up in the image of my last post. Since we have a sample file, can you point out what it should be?

Best Regards,
Dale

[Anonymous]

https://1drv.ms/u/s!ArzjP0-m7lkYw1cpMrDQpR-572Nr