Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Solved! Go to Solution.
HI @MAJackson ,
you can download my proposed solution from here.
I made a few changes:
1) I slightly modified the measure formula to match the initial formula:
Number of Active Employees = VAR currentDate = MAX('Calendar'[Date]) RETURN SUMX ('Hub Roster', VAR HubStartDate = [HubStartDate] VAR HubRollOffDate = [HubRollOffDate] RETURN IF (HubStartDate<=currentDate && OR(HubRollOffDate>=currentDate,HubRollOffDate=BLANK() ),1,0))
2) I removed the relationship between the Calendar table and the Employees table. As the Employees table has multiple dates (start date and end date), it's easier to do the filters using DAX than via relationship.
3) I added a calculated column in the Calendar table to check whether the month corresponds to the past 12 months rolling. If yes, it's TRUE otherwise it's FALSE. This column is used the filter the chart automatically
Here is the formula:
12 previous months = VAR currentMonth = VALUE(FORMAT(TODAY(),"mm")) VAR currentYear = YEAR(TODAY()) VAR currentMonthIsJan = currentMonth =1 VAR MonthMinus1 = IF(currentMonthIsJan,12,currentMonth-1) VAR YearMinus1 = IF(currentMonthIsJan, currentYear-1,currentYear) VAR YearMonthMinus1 = VALUE(YearMinus1&FORMAT(MonthMinus1,"00")) VAR YearMonthMinus12 = VALUE((currentYear-1)&FORMAT(currentMonth,"00")) VAR isWithin12Months = [MonthYearNum]>=YearMonthMinus12 && [MonthYearNum]<=YearMonthMinus1 RETURN isWithin12Months
4) I added a new measure to calculate the number of New Employees:
Number of New Employees = VAR currentDate = MAX('Calendar'[Date]) VAR currentYearMonth = VALUE(YEAR(currentDate)&FORMAT(currentDate,"mm")) RETURN SUMX ('Hub Roster', VAR HubStartDate = [HubStartDate] VAR HubRollOffDate = [HubRollOffDate] VAR HubStartDateYearMonth = VALUE(YEAR([HubStartDate])&FORMAT([HubStartDate],"mm")) RETURN IF (HubStartDateYearMonth=currentYearMonth ,1,0))
5) And finally a measure for Active employees excluding New Employees:
Number of Active Employees minus New = [Number of Active Employees]-[Number of New Employees]
Here is what it looks like:
Hopefully, this is exactly what you are looking for!
Do not hesitate if you have further questions,
LC
Interested in Power BI and DAX tutorials? Check out my blog at www.finance-bi.com
Hi @MAJackson ,
We can create a measure use the following formula to meet your requirement.
Number of Active Employees = COUNTROWS ( FILTER ( ALLSELECTED ( 'Hub Roster' ), AND ( 'Hub Roster'[HubStartDate] <= MAX ( 'Calendar'[Date] ), OR ( 'Hub Roster'[HubtollofDate] >= MIN ( 'Calendar'[Date] ), ISBLANK ( 'Hub Roster'[HubtollofDate] ) ) ) ) )
BTW, if you use ALLSELECTED ( 'Hub Roster' ) in lc_finance's formula, you can get similar result.
Best regards,
Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello All,
Thank you for your insights on my problem. Unfortunatly, I have not been able to get the data to conform the correct way. I am attaching links to a Sample Power BI file and the dummy data that is in the same format as the real data below:
The Sample Bi File has the data sets already setup with one calculated measure showing New employees per month. However when I drill into septermber should show 11 employees and not 4. I am trying to accomplish the following:
If you have any suggestions on how to accomplish the above it would be greatly appreciated.
Again, thank you for your time and advice!
Matthew
HI @MAJackson ,
you can download my proposed solution from here.
I made a few changes:
1) I slightly modified the measure formula to match the initial formula:
Number of Active Employees = VAR currentDate = MAX('Calendar'[Date]) RETURN SUMX ('Hub Roster', VAR HubStartDate = [HubStartDate] VAR HubRollOffDate = [HubRollOffDate] RETURN IF (HubStartDate<=currentDate && OR(HubRollOffDate>=currentDate,HubRollOffDate=BLANK() ),1,0))
2) I removed the relationship between the Calendar table and the Employees table. As the Employees table has multiple dates (start date and end date), it's easier to do the filters using DAX than via relationship.
3) I added a calculated column in the Calendar table to check whether the month corresponds to the past 12 months rolling. If yes, it's TRUE otherwise it's FALSE. This column is used the filter the chart automatically
Here is the formula:
12 previous months = VAR currentMonth = VALUE(FORMAT(TODAY(),"mm")) VAR currentYear = YEAR(TODAY()) VAR currentMonthIsJan = currentMonth =1 VAR MonthMinus1 = IF(currentMonthIsJan,12,currentMonth-1) VAR YearMinus1 = IF(currentMonthIsJan, currentYear-1,currentYear) VAR YearMonthMinus1 = VALUE(YearMinus1&FORMAT(MonthMinus1,"00")) VAR YearMonthMinus12 = VALUE((currentYear-1)&FORMAT(currentMonth,"00")) VAR isWithin12Months = [MonthYearNum]>=YearMonthMinus12 && [MonthYearNum]<=YearMonthMinus1 RETURN isWithin12Months
4) I added a new measure to calculate the number of New Employees:
Number of New Employees = VAR currentDate = MAX('Calendar'[Date]) VAR currentYearMonth = VALUE(YEAR(currentDate)&FORMAT(currentDate,"mm")) RETURN SUMX ('Hub Roster', VAR HubStartDate = [HubStartDate] VAR HubRollOffDate = [HubRollOffDate] VAR HubStartDateYearMonth = VALUE(YEAR([HubStartDate])&FORMAT([HubStartDate],"mm")) RETURN IF (HubStartDateYearMonth=currentYearMonth ,1,0))
5) And finally a measure for Active employees excluding New Employees:
Number of Active Employees minus New = [Number of Active Employees]-[Number of New Employees]
Here is what it looks like:
Hopefully, this is exactly what you are looking for!
Do not hesitate if you have further questions,
LC
Interested in Power BI and DAX tutorials? Check out my blog at www.finance-bi.com
Thank you very much! I was able to get the test data to look exactly like I wanted it to (see image).
I am now working to replicate this in the actual report with the final data. I updated the graph to show OCT in the image but did just read your note. It makes perfect sense and will adjust in the final report to your recommendation.
I am glad I asked as this multi level solution would have taken me a long while to figure out. I really appreciate your time and feedback!
Matthew
Additionally- in the proposed solution the months visualized stop at the previous month.
Example: we are in October 2019 and the months visualized stop at September 2019 (not October).
This ensures that the chart always shows the final numbers (until the 1st of November, we will not know for sure the new hires of October).
Let me know if this makes sense to you,
LC
Hi,
Share some data and show the expected result.
Hi @MAJackson ,
You can obtain that with a calculated measure. Here is the formula you can use:
Number of Active Employees = VAR currentDate = MAX('Date'[Date]) RETURN SUMX('Employees', VAR employeeStartDate = [Start Date] VAR employeeEndDate = [End Date] RETURN IF(employeeStartDate<=currentDate && OR(employeeEndDate>=currentDate, employeeEndDate=BLANK() ),1,0) )
Below a screenshot of what it would look like:
And here is a Power BI file with an example.
Let me know if this answers your questions.
Regards,
LC
Interested in Power BI templates? Check out my blog at www.finance-bi.com
Hello,
I tried the formula but I did not yeild a result (see image below). Could you let me know what I am missing?
Thank you!
Matthew
Hello,
I refreshed my date table and got the below result. It is still not the number of employees that were on each month. I think this may be the number of new employees? Could you let me know what corrections I may need to make.
Thank you very much for your help!
Hi @MAJackson,
Could you share a sample Power BI file?
You can share it via OneDrive, Google Drive, Dropbox or similar tools.
I'll take a look at it and help you understanding what's missing.
Regards,
LC
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
98 | |
78 | |
65 | |
60 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |