Getting minimum values out of calculated table

Question

I have:

1. a table with user names

2. a table indicating actions with columns for user name, action time, action name. Named events unique_events

I started collecting data on January. I want to have a column in my table of user names which indicates how long it has been since a user first used my application and the first of January.

So if a user first logged in in January, the value of the row with that user's name will be 0. If one logged in on March it will be 2.

I tried:

Column = DATEDIFF(01-01-2016, MIN(SELECTCOLUMNS(FILTER('events unique_events','events unique_events'[User Name] = Users[User Name]),"DatedTime", [DatedTime])),MONTH)

which returns an error saying the Min function needs a column reference.

I also tried the same with FirstDate instead of MIN which returned an error saying FirstDate can't be used with summarize functions.

Any other ideas on how to achieve this, or fix what I tried?

Answer 1

(for simplicity, I will call your table 'Events', and user login dates field 'User_Login_Date').

First, define your app start date as a measure:

App_Start_Date:= DATE(2016, 1, 1)

Then, define measure that finds min differences between Application Start Date and User Login dates:

User_Start_Diff=: MINX(Events, DATEDIFF([App_Start_Date], Events[User_Login_Date], Month))

Drop this measure into a pivot table against user names, and you should have your desired result.

How it works: 1) MINX goes record by record and calculates date differences for each customer login. It then finds minimum in the results;

2) When you drop the measure into a pivot table, it splits MINX results by customer, and recalculates min for each of them separately. You don't need to do the grouping.

Creation of [Start_Date] measure is not technically necessary but a matter of good style - don't hardcode values in your formulas, always create measures. You will thank yourself later when you need to make a change.