cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
leont3
Helper II
Helper II

Last 6 Months DAX Measure - Based on Date Field

I am having a bit of difficulty trying to get my head around having data on the last six months based on a date field in another table

 

Scenario: 

I am looking at employees and the number of shifts that they do. I only want to count how many shifts they have completed in the last 6 months of their 'anniversary date'. I have tried using the DATESINPERIOD but it is not customised based on the anniversary date. I have the following tables:

  • Employee Data Table
Employee IDAnniversary Date
130 June 2021
223 June 2021
34 September 2021

 

  • Shift Information
Employee IDShift Date
129 June 2021
11 January 2021
220 June 2021
231 December 2020
320 May 2021
33 July 2021

 

  • Date Table

I have set up the following measure as well: 

Number of Shifts = COUNTROWS(Shift Information)

 

I would like to count the number of shifts that employees have done in the last 6 months from their anniversary date, so this is what I would like the expected output to be: 

Employee IDNumber of Shifts
11
21
32

 

As you can see any shifts that have fallen out of the six month period (of the employee's anniversary date) are not included.

 

What would you recommend the next steps be?

 

Thank you! 

1 ACCEPTED SOLUTION
v-yalanwu-msft
Community Support
Community Support

Hi, @leont3 ;

As lbendlin Said , you offer data is all in last 6 months. if you want to according to every Employee ID's Anniversary Date to count . the measure is as follows ( the result is also different with you) :

countrow = CALCULATE(COUNTROWS('Shift'),FILTER('Shift',DATEDIFF([Shift Date],RELATED(Employee[Anniversary Date]),MONTH)<=6))

vyalanwumsft_0-1632113074257.png

The final output is shown below:

vyalanwumsft_1-1632113082473.png

However, if you want to show the result as yours. you could modify the measure like below:

countrow2 = CALCULATE(COUNTROWS('Shift'),FILTER('Shift',DATEDIFF([Shift Date],MAX(Employee[Anniversary Date]),MONTH)<=6))

The final output is shown below:

vyalanwumsft_2-1632113205496.png


Best Regards,
Community Support Team_ Yalan Wu
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

2 REPLIES 2
v-yalanwu-msft
Community Support
Community Support

Hi, @leont3 ;

As lbendlin Said , you offer data is all in last 6 months. if you want to according to every Employee ID's Anniversary Date to count . the measure is as follows ( the result is also different with you) :

countrow = CALCULATE(COUNTROWS('Shift'),FILTER('Shift',DATEDIFF([Shift Date],RELATED(Employee[Anniversary Date]),MONTH)<=6))

vyalanwumsft_0-1632113074257.png

The final output is shown below:

vyalanwumsft_1-1632113082473.png

However, if you want to show the result as yours. you could modify the measure like below:

countrow2 = CALCULATE(COUNTROWS('Shift'),FILTER('Shift',DATEDIFF([Shift Date],MAX(Employee[Anniversary Date]),MONTH)<=6))

The final output is shown below:

vyalanwumsft_2-1632113205496.png


Best Regards,
Community Support Team_ Yalan Wu
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

lbendlin
Super User
Super User

@leont3 maybe it is just me but I think all of the shifts are within the six months up to the anniversary date for each employee?

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.