Group user transactions per period of time according to hire date (0-3 months), (3-6 months)

[mpmsltd]

Hi All,

I have a table the shows user transactions and another table that shows the user id and hire date.

I need to count the transactions by groups that correspond to days after hired (0-90 days, 90-180 days and + 180 days). Can someone please help me to get to the expected result below? Thanks!

Example

user id
abc	1/1/2019
def	3/1/2019
ghi	4/1/2019
Transactions
abc	1/2/2019
abc	1/2/2019
abc	5/5/2019
def	3/1/2019
def	4/1/2019
def	10/1/2019
def	10/2/2019
ghi	4/1/2019

Expected Result

user id	0-90	90-180	180
abc	2	1
def	2		2
ghi	1

[JarroVGIT · Accepted solution]

Hi @mpmsltd ,

I think the easiest way is to add the three columns to your first table with the following calculated columns:

0-90 = 
VAR _curUserID = HireTable[user id]
VAR lowerDate = HireTable[HireDate]
VAR upperDate = HireTable[HireDate]+90 
RETURN
COUNTROWS(FILTER(Transactions, Transactions[user id] = _curUserID && Transactions[TransactionDate] >= lowerDate && Transactions[TransactionDate] <= upperDate))

91-180 = 
VAR _curUserID = HireTable[user id]
VAR lowerDate = HireTable[HireDate] + 91
VAR upperDate = HireTable[HireDate] + 180 
RETURN
COUNTROWS(FILTER(Transactions, Transactions[user id] = _curUserID && Transactions[TransactionDate] >= lowerDate && Transactions[TransactionDate] <= upperDate))

180 + = 
VAR _curUserID = HireTable[user id]
VAR lowerDate = HireTable[HireDate] + 181
RETURN
COUNTROWS(FILTER(Transactions, Transactions[user id] = _curUserID && Transactions[TransactionDate] >= lowerDate))

This results in this:

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

[mpmsltd]

That's a great solution! Thanks!