How to calculate Starters and Leavers |DatesinPeriod |CountRows|

[MYDATASTORY]

Hi,

Please advice,

I am trying to calculate below

• Players who have joined the club.
• Players who have left the club

I have two tables (Player and Contract)

• Player table have player details/attributes (Player Id (Unique), Player Name)
• Contract table have Player contract details/attributes (Contract Id, Start date, End Date, Contract Reference id)

Current logic:

• Contract Id (Contact table) Join Player Id (Player table)
• Each player can have many contracts (one to many)

I would like to calculate below

• These players who have joined (Starters) the club (Use start dates)
• These players who have left (Leavers)the club        (Use Start date and End date)

Please note I have created a Calendar table (Power BI) for the data model which connects the contract table Start date, this is for modeling purposes so that I can one central calendar for slicing the data.

Current Dax which need advice in expanding it  

Starters:  If (contract [start date])>Today (),1,0 

 updated dummy data and I would expect to see  3 starters and 6 leavers based on the below table. 

Player Id	Contract ID	Contract Reference ID	Start DateEnd	End Date	Valid Contract
1	1	REF1	01/08/2020	01/08/2021	1
2	2	REF2	01/08/2019	01/08/2022	1
2	2	REF3	01/07/2017	01/08/2019	0
4	4	REF4	01/09/2019	01/09/2020	1
4	4	REF5	01/07/2017	01/08/2019	0
6	6	REF6	01/01/2020	01/08/2020	0
7	7	REF7	02/01/2020	02/09/2020	1
8	8	REF8	03/01/2020	03/01/2020	0
9	9	REF9	04/01/2020	04/01/2020	0
10	10	REF10	05/01/2020	05/01/2020	0

I have also use CountRows function and DatesinPeriod function

Thanks in advance

[v-rzhou-msft]

Hi @MYDATASTORY 

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your calculate logic and your problem or share me with your pbix file from your Onedrive for Business.

Best Regards,

Rico Zhou

[MYDATASTORY]

Hi @v-rzhou-msft  thanks so much, not yet, let me get more dummy data and I can share, but definitely in the right direction, I will come back to you shortly once I have recompiled a more comprehensive dummy data and share a pbix file with dummy data.

[v-rzhou-msft · Accepted solution]

Hi @MYDATASTORY 

I use your sample table and build a calender table.

Build a slicer by Date value in calendar table.

calendar = CALENDAR(MIN('Table'[Start DateEnd]),MAX('Table'[End Date]))

Build a relationship between two tables: 

I build a calculated column to mark the player whose End Date > today() be Valid Contract.

Valid Contract = IF('Table'[End Date]>=TODAY(),1,0)

Result:

And make people who has valid contract be Starters, people who don't have any valid contract be Leavers.

M_Valid Contract = IF(MAX('Table'[End Date])>=TODAY(),1,0)

Starter = 
var _min = MINX(calendar,calendar[Date])
var _max = MAXX(calendar,calendar[Date])
return
CALCULATE(DISTINCTCOUNT('Table'[Player Id ]),FILTER('Table',_min<= 'Table'[Start DateEnd]&&_max>= 'Table'[End Date]&&[M_Valid Contract]=1))

Measure = 
SUMX(FILTER(ALL('Table'),'Table'[Player Id ]=SUM('Table'[Player Id ])),[M_Valid Contract])

Leaver = 
var _min = MINX(calendar,calendar[Date])
var _max = MAXX(calendar,calendar[Date])
return
CALCULATE(DISTINCTCOUNT('Table'[Player Id ]),FILTER('Table',_min<= 'Table'[Start Date]&&_max>= 'Table'[End Date]&&[Measure]=0))

Result:

Default

Date between 2019/12/31 and 2020/12/31

If my reply still couldn't help you solve your problem, please show me more details about your request.

Could you show me your logic just like which 3 players will be starters and which 6 players will be leavers? I can find only 8 different players in your example.  And please tell me your logic to judge a player a starter or a leaver. You can share your pbix file with me by your OneDrive for business.

You can download the pbix file from this link: How to calculate Starters and Leavers

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[amitchandak]

@MYDATASTORY , Can you share sample data and sample output in table format?

See if this can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[MYDATASTORY]

@amitchandak  Thanks for the link, please see the updated table with dummy data.