cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
garynorcrossmmc
Helper I
Helper I

Same Period Prior Year with Dynamic Date Range

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)]))
)
 
I want to display a Bar/Line graph that shows this measure as the bar and 1 year prior as the line above the bars.  However, when I create this measure, the bar/line graph shows incorrectly (shown below).  How can I fix this?
 
## - 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)])
)
Capture.PNG
1 ACCEPTED SOLUTION
v-robertq-msft
Community Support
Community Support

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:

v-robertq-msft_0-1618905099370.pngv-robertq-msft_1-1618905099371.png

 

  1. I created two measures like this:
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))
  1. Then I created a “date between” slicer and a table chart to place them like this:

v-robertq-msft_2-1618905099374.png

v-robertq-msft_3-1618905099381.png

 

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.

View solution in original post

6 REPLIES 6
v-robertq-msft
Community Support
Community Support

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:

v-robertq-msft_0-1618905099370.pngv-robertq-msft_1-1618905099371.png

 

  1. I created two measures like this:
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))
  1. Then I created a “date between” slicer and a table chart to place them like this:

v-robertq-msft_2-1618905099374.png

v-robertq-msft_3-1618905099381.png

 

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.

View solution in original post

Success!  Thank you, @v-robertq-msft!!!

Ashish_Mathur
Super User III
Super User III

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]))

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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:
Capture.PNG 

And here is the result I'm looking for if these dates are applied as filters:

Capture2.PNG

 

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

 

Screenshot (8).png

 

Please let me know if you need any other information.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors