cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
mpmsltd Frequent Visitor
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 
abc1/1/2019
def3/1/2019
ghi4/1/2019
  
  
Transactions 
abc1/2/2019
abc1/2/2019
abc5/5/2019
def3/1/2019
def4/1/2019
def10/1/2019
def10/2/2019
ghi4/1/2019

 

Expected Result

user id0-9090-180180
abc21 
def2 2
ghi1  

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User I
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:

image.png

 

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


Did I answer your question? Mark my post as a solution!
Kudo's are appreciated and considered a motivation.

Proud to be a Datanaut!

View solution in original post

2 REPLIES 2
Super User I
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:

image.png

 

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


Did I answer your question? Mark my post as a solution!
Kudo's are appreciated and considered a motivation.

Proud to be a Datanaut!

View solution in original post

mpmsltd Frequent Visitor
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!

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

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!

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?

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.

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors