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.
Hi all,
I have a measure that looks at the selected date range, specified in the FILTER function:
## - Hire Count (Regular) = CALCULATE( SUM('HR AGG_WORKFORCEEVENT'[HIRECNT])-SUM('HR AGG_WORKFORCEEVENT'[ACQUISITION]), 'HR DIM_EMPLOYMENT'[Employment Type Flag]=1, FILTER('HR AGG_WORKFORCEEVENT', 'HR AGG_WORKFORCEEVENT'[Date (End of Month)] <= max('HR DIM_MONTH'[Date (End of Month)]) && 'HR AGG_WORKFORCEEVENT'[Date (End of Month)] > min('HR DIM_MONTH'[Date (End of Month)])) ) |
## - Hire Count (-1 Year) = CALCULATE(SUM('HR AGG_WORKFORCEEVENT'[HIRECNT])-SUM('HR AGG_WORKFORCEEVENT'[ACQUISITION]), 'HR DIM_EMPLOYMENT'[Employment Type Flag]=1, SAMEPERIODLASTYEAR('HR DIM_MONTH'[Date (End of Month)]), REMOVEFILTERS('HR DIM_MONTH'[Date (End of Month)]) ) |
Solved! Go to Solution.
Hi, @garynorcrossmmc
According to your description and sample pictures, I can clearly understand your requirement, I’ve also created some data to achieve your need, you can try my steps:
This is part of the test data I created based on your description:
Sum of HIRECNT = SUM('HR AGG_WORKFORCEEVENT'[HIRECNT])
Sum of HIRECNT (1 Year Prior) =
var _max=MAX('HR DIM_MONTH'[Date])
var _min=MIN('HR DIM_MONTH'[Date])
return
CALCULATE(
SUM('HR AGG_WORKFORCEEVENT'[HIRECNT]),
FILTER(ALL('HR AGG_WORKFORCEEVENT'),
[Date]>=DATE(YEAR(_min)-1,MONTH(_min),DAY(_min))&&
[Date]<=DATE(YEAR(_max)-1,MONTH(_max),DAY(_max))&&
[Date (End of month)]=FORMAT(MAX([Date]),"MMMM")&" "&YEAR(MAX([Date]))-1))
And as you can see from the last picture, you can get what you want with the correct data displayed.
You can download my test pbix file below
If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.
How to Get Your Question Answered Quickly
Thank you very much!
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @garynorcrossmmc
According to your description and sample pictures, I can clearly understand your requirement, I’ve also created some data to achieve your need, you can try my steps:
This is part of the test data I created based on your description:
Sum of HIRECNT = SUM('HR AGG_WORKFORCEEVENT'[HIRECNT])
Sum of HIRECNT (1 Year Prior) =
var _max=MAX('HR DIM_MONTH'[Date])
var _min=MIN('HR DIM_MONTH'[Date])
return
CALCULATE(
SUM('HR AGG_WORKFORCEEVENT'[HIRECNT]),
FILTER(ALL('HR AGG_WORKFORCEEVENT'),
[Date]>=DATE(YEAR(_min)-1,MONTH(_min),DAY(_min))&&
[Date]<=DATE(YEAR(_max)-1,MONTH(_max),DAY(_max))&&
[Date (End of month)]=FORMAT(MAX([Date]),"MMMM")&" "&YEAR(MAX([Date]))-1))
And as you can see from the last picture, you can get what you want with the correct data displayed.
You can download my test pbix file below
If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.
How to Get Your Question Answered Quickly
Thank you very much!
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
Assuming that the Month/Year on the X-axis come from the Date Table and there is a Many to One (Single) relationship from the Date column of your Data Table to the Date column of the Date Table, these measures should work
## - Hire Count (Regular) =
CALCULATE(
SUM('HR AGG_WORKFORCEEVENT'[HIRECNT])-SUM('HR AGG_WORKFORCEEVENT'[ACQUISITION]),
'HR DIM_EMPLOYMENT'[Employment Type Flag]=1)
=calculate([## - Hire Count (Regular)],sameperiodlastyear(Date[Date]))
HI @Ashish_Mathur, thank you for the reply, however I think I should've mentioned that the sum needs to take all dates > than the MIN date and <= the MAX date. So for example, if you see the bar/line graph in my original post, that is filtered for > 3/31/2020 and <= 3/31/2021. This is why my first measure has the FILTER function included in the CALCULATE function.
My measures are pulling in the numbers correctly, it's just that I can't get them to display correctly on the bar/line graph.
Hi,
The FILTER() function should still not be required. I can offer further help, if you can show me the expected result in a simple Table format. Once we get the result in a Table format, we can always switch to whatever visual we want.
Hi again @Ashish_Mathur. What I'm trying to accomplish here is to create a measure that sums up a field called HIRECNT (total hiring events) by month. This would be straightforward, except that the user has requested that the report only shows the hires that took place after the inputted Start Date and on or before the inputted End Date. So at the top of the report, I have Start and End Date slicers:
And here is the result I'm looking for if these dates are applied as filters:
The HIRECNT field is on a table called 'HR AGG_WORKFORCEEVENT' which is has a relationship to a date table called 'HR DIM_MONTH' via the Date field
Please let me know if you need any other information.
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 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |