Create a match in two tables based on previous and current month

[ny326n]

Hi everyone!

I have two tables, one with the creation date of client accounts:

Client ID, Creation date

The second table includes created assignements:

Client ID, Assignment ID, Assignment creation date

What I want to do is to calculate the percentage of clients who created their account in previous month who placed an assignment in current month. I also want to calculate the average time from account registration to first creation of assignment.

Anyone got any suggestions on how to solve this?

Thank you very much in advance!

[v-huizhn-msft · Accepted solution]

Hi @ny326n,

First, please create a relationship between the table.

Second, in second table, you create a calculated column using RELATED function as the following formula.

create-date=RELATED(Table1[Creation date])

Third, create another calculated column using the formula, which shows if the account was created in previous month.

flag=IF(MONTH(Assignment creation date)-MONTH(create-date)>=1,"Yes","No")

Create a measure to calculate the percentage of clients who created their account in previous month who placed an assignment in current month.

Percentage=CALCULATE(DISTINCTCOUNT(Table2[ClientID]),FILTER(Table2,Table[flag]="Yes"))/CALCULATE(DISTINCTCOUNT(Table2[ClientID]),ALL(Table2))

>>I also want to calculate the average time from account registration to first creation of assignment.

Create a calculated to get the difference using the formula.

difference=Table2[create-date]-Table2[Assignment creation date]

Create a measure using the formula.

Average=AVERAGE(Table2[difference])

Best Regards,
Aneglia

[ny326n]

Thank you for your help! It worked perfectly!