I'm trying to create a Dax Calculation where it gives me the average number of dates entered monthly for the company or for specific employees selected in a slicer. If an employee is selected in the slicer, it must take into account the employees start date, so if a time range is selected before the employee started working, then it will dynamically calculate based on the number of months the employee has been employed.
For example, an employee starts work on December 1st 2022 and enters 2 dates in December and 4 dates in January. The company as a whole entered 6 dates in November, 9 dates in December and 12 dates in January.
If the enduser selects November through January in the slicer, the company average will be 9 dates entered per month. Then if the enduser selects the employee, it will show 3 dates entered per month because it's not including November which is the month before the employee started.
If in February the company and employee both have 0 dates entered, the company calculation would be (6+9+12+0)/4 = 6.75 and the employee calculation would be (2+4+0)/3 = 2.
I was able to come up with a calculation that works for last year when an employee is selected, but when an employee is not selected, it still uses the max employee start date for the company. This is also for the entire year and does not work when selecting months in the slicer.
Leads Assigned Weekly Average Last Year = IF(YEAR(MAX('SDRs (Accounts)'[Start Date]))=YEAR(TODAY())-1,DIVIDE([Leads Assigned],52-WEEKNUM(MAX('SDRs (Accounts)'[Start Date]))),DIVIDE([Leads Assigned],52))
Leads Assigned =CountA(Lead Date)
