Hope your all good…
I have scenario where I need to calculate the number of hires made by recruiter in 2022 until the start of week that is until Monday = 10-Jan-2022 and I want compare the same with last year hire done by him until or before Monday - 10-Jan-2021,
I have was able to restrict the data of current year hires using power query but not sure how will I able to restrict the same for last year.
You can see in the table for ID-111 the last year-2021 number should be 1 and for current year its is showing correctly as 3
I took a shot at a solution for you. A couple of things added was marking the Date Table as such and adding dimension table for your recruiters. The measures are very similar to yours. Please see attached.
I hope this helps! Recruiters_Forum.pbix (121.8 KB)
I’ve been giving this one a go too and @Whitewater100 sneaked in. Just fantastic this forum.
So a few things:
I’ve marked the date table as a date table - this enables time intelligence to function
Created PY count measure - initially incorrect totals in matrix.
created PY Correct measure - sorts out subtotalling.
This one was a bit of a learning for me too. I hadn’t expected a totalling issue on Countrows at all.
If you change the rows and columns on the matrix you will need to change the DAX formula to get the total correct again. So check out @BrianJ video on correcting totals.
Thank you Pete for sharing that piece about Matrix totals. Your solution for LY totals is correct!
I’ll check out Brian’s video as I was not aware of this occurrence. Thanks again for pointing out where to more fully understand this type of thing.
Expanding on Pete’s idea, you may find using the measure “New Hire LY Fix” which essentially provides the same results with a bit less coding. Fix Matrix Totals LYRecruiters_Forum.pbix (132.3 KB)
Just fantastic this forum.