Same Period Prior Year with Dynamic Date Range

[garynorcrossmmc]

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

)

[v-robertq-msft · Accepted 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:

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

2. Then I created a “date between” slicer and a table chart to place them like this:

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.

[garynorcrossmmc]

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

[Ashish_Mathur]

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

[garynorcrossmmc]

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.  

[Ashish_Mathur]

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.

[garynorcrossmmc]

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.