Average Monthly Count dynamically with Start Dates
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)
Problems with using COUNTA with multiple choice cells from a Microsoft List
Good afternoon. I am having some issues using COUNTA when counting populated column cells in data view that contains text from a Microsoft List that uses a choice selection (Yes, No, or left blank) in the column cells. It appears that the COUNTA syntax is reading that the cells in the columns have text in them even thought they are blank. I beleive it may be detecting that the column cells have a choice fuction in them from the Microsoft List, even when they do not have any text in them currently. I can see the blank cells in the data view, but they are still being counted as populated in the report view. The COUNTA syntax is working correctly for my column cells that use a date in them. Below is the COUNTA syntax I am using. Any insight on why this is occuring and how to work around this issue would be greatly appreciated. Thank you for your help. Complete = COUNTA('ALL'[AS9100]) + COUNTA('ALL'[FGOPS]) + COUNTA('ALL'[FOD Control Calculated]) + COUNTA('ALL'[Tool Control Calculated]) + COUNTA('ALL'[GSE]) + COUNTA('ALL'[Tow Operations]) + COUNTA('ALL'[Jacking Operations Calculated]) + COUNTA('ALL'[Mooring/Hangaring/Security]) + COUNTA('ALL'[Tire/Wheel]) + COUNTA('ALL'[Battery]) + COUNTA('ALL'[Corrosion]) + COUNTA('ALL'[HAZMAT]) + COUNTA('ALL'[Safe-for-Maintenance]) + COUNTA('ALL'[Egress Calculated]) + COUNTA('ALL'[W&B]) + COUNTA('ALL'[Technical Data (orders, manuals, IETMs)]) + COUNTA('ALL'[Aircraft Records Management]) + COUNTA('ALL'[CADS Calculated]) Best regards, Brian
Creating Measures for distinct count between 2 custom dates
I have a dataset of leads with unique IDs and lead dates for the past 2 years. However, the years are not based on the calendar year, but a custom time frame 8/1 - 7/31. I also created a date table. I would like to create measures that do the following - Prior Year leads - distinct count of IDs for lead date between 8/1/19 - 7/31/2020 Current Year leads - distinct count of IDs for lead date between 8/1/20 - 7/31/2021 Prior Year Leads to date - count of prior year leads up to current date I was able to do this with the data separated into 2 queries using DAX below, but was unsure how to accomplish it with mixed data set. Total CY Leads = COUNTA(CY_Leads[CONTACTID]) Total PY Leads = COUNTA(PY_Leads[CONTACTID]) Thank you