Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
i have a table with columns as "project", "name", "start date" and "end date".
1. i wanted to count the number of employees who has joined the project and also who has left the project per month.
Exmaple: if 10 employees joined the project in jan and 8 in Feb and 12 in march, and 3 left the project in jan, 4 in feb, and 5 in March. i want the result to be shown as, number of employes in jan= 10-3=7, Feb= 7(from jan)+8-4=11, march= 11(from Feb)+12-5=18.
for this i have calculated cumulative count of employees who has joined the project:
Solved! Go to Solution.
Hi @Anonymous
Based on my test, i've added index column in query editor for date table. Below are measures for your reference.
Startcount = CALCULATE(COUNTROWS('Sample'))
Endcount = CALCULATE(COUNTROWS('Sample'),USERELATIONSHIP('Sample'[End Date],'Date'[Date]))
Leftcount = [Startcount]-[Endcount]
Final = VAR a = SUMMARIZE ( FILTER ( ALL ( 'Date' ), 'Date'[Index] <= MAX ( 'Date'[Index] ) ), 'Date'[Index], 'Date'[Month], "b", [Leftcount] ) RETURN SUMX ( a, [Leftcount] )
Regards,
Cherie
Hi @Anonymous
please follow these steps:
After you have loaded your table ( named Data ) into Power BI, create a new table ( named DataModified) with the following DAX Query:
DataModified = SELECTCOLUMNS( GENERATE( Data, VAR StartDate = Data[Start Date] VAR EndDate = Data[End Date] RETURN CALENDAR( StartDate, EndDate - 1 ) ), "Project", Data[project], "Name", Data[Name], "Date", [Date] )
Afterwards create the calendar, projects and names dimensions:
Calendar = ADDCOLUMNS( CALENDAR( DATE( 2017, 1, 1 ), DATE( 2019, 12, 31 ) ), "Month", MONTH( [Date] ), "Month Name", FORMAT( [Date], "MMMM" ), "Year", YEAR( [Date] ), "Month Year Name", FORMAT( [Date], "mmm yyyy" ), "Month Year Number", YEAR( [Date] ) * 100 + MONTH( [Date] ) )
Active Employees = CALCULATE( COUNTROWS( DataModified ), LASTDATE( 'Calendar'[Date] ) )
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
@Anonymous
Would you be able to post a sample data along with expected results ?
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
@LivioLanzo and @Greg_Deckler .
Please fins the below sample data with results required.
project Name Start date End Date
1 Ravi 29-10-2018 31-12-2018
1 akash 29-10-2018 31-12-2018
1 vinay 29-10-2018 31-12-2018
1 nag 29-10-2018 31-12-2018
1 venkata 29-10-2018 31-12-2018
1 suma 12-11-2018 31-12-2018
3 rati 29-10-2018 31-12-2018
3 laxmi 12-11-2018 31-12-2018
3 lucky 03-12-2018 31-12-2018
3 hema 03-12-2018 31-12-2018
3 anju 12-11-2018 31-12-2018
3 dev 03-12-2018 31-12-2018
3 devid 29-10-2018 31-12-2018
3 rahul 29-10-2018 31-12-2018
3 kahlid 29-10-2018 31-12-2018
3 john 29-10-2018 31-12-2018
3 cristi 12-11-2018 21-12-2018
3 ali 01-11-2018 18-01-2019
3 suhas 01-11-2018 18-01-2019
1 veer 01-11-2018 18-01-2019
2 pandu 01-11-2018 18-01-2019
2 kishan 22-10-2018 15-02-2019
2 surab 22-10-2018 15-02-2019
2 mohan 22-10-2018 15-02-2019
2 das 03-09-2018 29-03-2019
2 tomy 03-09-2018 29-03-2019
2 tony 03-09-2018 29-03-2019
2 sunil 03-09-2018 29-03-2019
2 sam 03-09-2018 29-03-2019
2 suraj 03-09-2018 29-03-2019
Results:
1)
Month No.of Emp Joined
Sep-18 6
Oct-18 13
Nov-18 8
Dec-18 3
Jan-19 0
Feb-19 0
Mar-19 0
Month No.of Emp Left
Sep-18 0
Oct-18 0
Nov-18 0
Dec-18 17
Jan-19 4
Feb-19 3
Mar-19 6
3)
Month Final Result logic
Sep-18 6 6-0
Oct-18 19 6+13-0
Nov-18 27 19+8-0
Dec-18 13 27+3-17
Jan-19 9 13+0-4
Feb-19 6 9+0-3
Mar-19 0 6+0-6
Logic is just for reference.
Hi @Anonymous
please follow these steps:
After you have loaded your table ( named Data ) into Power BI, create a new table ( named DataModified) with the following DAX Query:
DataModified = SELECTCOLUMNS( GENERATE( Data, VAR StartDate = Data[Start Date] VAR EndDate = Data[End Date] RETURN CALENDAR( StartDate, EndDate - 1 ) ), "Project", Data[project], "Name", Data[Name], "Date", [Date] )
Afterwards create the calendar, projects and names dimensions:
Calendar = ADDCOLUMNS( CALENDAR( DATE( 2017, 1, 1 ), DATE( 2019, 12, 31 ) ), "Month", MONTH( [Date] ), "Month Name", FORMAT( [Date], "MMMM" ), "Year", YEAR( [Date] ), "Month Year Name", FORMAT( [Date], "mmm yyyy" ), "Month Year Number", YEAR( [Date] ) * 100 + MONTH( [Date] ) )
Active Employees = CALCULATE( COUNTROWS( DataModified ), LASTDATE( 'Calendar'[Date] ) )
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
This worked for me like a charm. But I had to remove the -1 as it gave error that end date cannot before start date.
CALENDAR( StartDate, EndDate - 1 )
Plus I really want to know how this works, to grow my understanding about dax.
Hi @Anonymous
Based on my test, i've added index column in query editor for date table. Below are measures for your reference.
Startcount = CALCULATE(COUNTROWS('Sample'))
Endcount = CALCULATE(COUNTROWS('Sample'),USERELATIONSHIP('Sample'[End Date],'Date'[Date]))
Leftcount = [Startcount]-[Endcount]
Final = VAR a = SUMMARIZE ( FILTER ( ALL ( 'Date' ), 'Date'[Index] <= MAX ( 'Date'[Index] ) ), 'Date'[Index], 'Date'[Month], "b", [Leftcount] ) RETURN SUMX ( a, [Leftcount] )
Regards,
Cherie
@v-cherch-msft Im not getting the same results as you, could you please share the pbix file.
Hi @Anonymous
Please follow these steps:
Once you have loaded your table (named Data) into Power BI, generate a new table (named DataModified) using the following DAX Query:
DataModified = SELECTCOLUMNS( GENERATE( Data, VAR StartDate = Data[Start Date] VAR EndDate = Data[End Date] RETURN CALENDAR( StartDate, EndDate - 1 ) ), "Project", Data[project], "Name", Data[Name], "Date", [Date] )
Afterwards, create a Date dimension, Project dimension and Names dimension tables:
Calendar = ADDCOLUMNS( CALENDAR( DATE( 2017, 1, 1 ), DATE( 2019, 12, 31 ) ), "Month", MONTH( [Date] ), "Month Name", FORMAT( [Date], "MMMM" ), "Year", YEAR( [Date] ), "Month Year Name", FORMAT( [Date], "mmm yyyy" ), "Month Year Number", YEAR( [Date] ) * 100 + MONTH( [Date] ) )
Active Employees = CALCULATE( COUNTROWS( DataModified ), LASTDATE( 'Calendar'[Date] ) )
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Sounds like you need a running total, there is a Quick Measure that you can use as a basis for that. Also, take a look at these two Quick Measures as I think you want something like them.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365
Greg_Deckler thank you so much for sharing your insight here. It is very helpful. Thank you!!
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |