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....I have created a power pivot where I want to count the number of the beginning headcount, number of hires, number of terms, and ending headcount as of that Sunday. From there, I will pull the same date for the four weeks prior to last week, and then YTD. I am really struggling with how to write an formula that will take that into account.
Date | Year | Month Number | Month | MMM-YYYY | Day Of Week Number | Day Of Week | Week Start Date | Week End Date | Previous Week Start Date | Previous Week End Date | Rolling 4 Weeks Start Date | Rolling 4 Weeks End Date |
11/1/2023 | 2023 | 11 | November | Nov-23 | 4 | Wednesday | 10/30/2023 | 11/5/2023 | 10/23/2023 | 10/29/2023 | 10/2/2023 | 10/22/2023 |
11/2/2023 | 2023 | 11 | November | Nov-23 | 5 | Thursday | 10/30/2023 | 11/5/2023 | 10/23/2023 | 10/29/2023 | 10/2/2023 | 10/22/2023 |
11/3/2023 | 2023 | 11 | November | Nov-23 | 6 | Friday | 10/30/2023 | 11/5/2023 | 10/23/2023 | 10/29/2023 | 10/2/2023 | 10/22/2023 |
11/4/2023 | 2023 | 11 | November | Nov-23 | 7 | Saturday | 10/30/2023 | 11/5/2023 | 10/23/2023 | 10/29/2023 | 10/2/2023 | 10/22/2023 |
11/5/2023 | 2023 | 11 | November | Nov-23 | 1 | Sunday | 10/30/2023 | 11/5/2023 | 10/23/2023 | 10/29/2023 | 10/2/2023 | 10/22/2023 |
11/6/2023 | 2023 | 11 | November | Nov-23 | 2 | Monday | 11/6/2023 | 11/12/2023 | 10/30/2023 | 11/5/2023 | 10/9/2023 | 10/29/2023 |
11/7/2023 | 2023 | 11 | November | Nov-23 | 3 | Tuesday | 11/6/2023 | 11/12/2023 | 10/30/2023 | 11/5/2023 | 10/9/2023 | 10/29/2023 |
I have created a calendar table that calculates the previous week start and end date based on the calendar date. But when I try to create the below measure, I get an error saying there are too many values. Any help would be appreciated!
=CALCULATE(COUNT(EmployeeTable[Hire/Rehire Date]),FILTER('EmployeeTable'[Hire/Rehire Date],'EmployeeTable'[Hire/Rehire Date]>=MIN('Calendar'[Previous Week Start Date])
&& 'EmployeeTable'[Hire/Rehire Date] <= MAX('Calendar'[Previous Week End Date])))
Hi @Bluestonegemini ,
Two quick questions:
1) Is there a relationship in place from your date column in EmployeeTable and the calendar table?
2) Does it work if you remove the MIN and MAX function in the filter part?
Let me know, if this helps 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on LinkedIn! |
#proudtobeasuperuser |
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.