cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Super User I

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

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! 🙂

Kudo's are appreciated and considered a motivation.

Proud to be a Datanaut!

2 REPLIES 2
Super User I

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

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! 🙂

Kudo's are appreciated and considered a motivation.

Proud to be a Datanaut!

Frequent Visitor

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

That's a great solution! Thanks!

Announcements

#### Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.