closing count
1 Topic
Correct the DAX measure closing count DAX measure
I have two tables. Allresources and DateTable. Below is the datetable created in Power BI Date MonthNo Month Yea Year 06-03-2024 00:00:00 3 Mar 24 2024 07-03-2024 00:00:00 3 Mar 24 2024 08-03-2024 00:00:00 3 Mar 24 2024 09-03-2024 00:00:00 3 Mar 24 2024 10-03-2024 00:00:00 3 Mar 24 2024 11-03-2024 00.00.00 3 Mar24 2024 12-03-2024 00:00:00 3 Mar24 2024 'Allresources' Table Division Onboarding Date Offboarding End Date Corp ID Governance 01-01-2024 30-08-2024 CHEPRASA Governance 15-01-2024 30-08-2024 STAVANAN SF 01-01-2024 07-09-2024 SUPV MTE 11-01-2024 08-12-2025 REKAMBI Governance 01-01-2024 31-12-2025 JAGAPATI SF 15-01-2024 31-12-2025 PRRASAL MCM 21-02-2024 31-12-2026 SWAROOM MCM 22-02-2024 05-12-2025 **bleep**ALSU SF 22-01-2024 08-12-2025 FEESINGH Both have a active relation with Date column in DateTable and OffBOarding End Date column in AllResources Table. Inactive realtionship with Date column in DateTable and OnBOarding Date column in AllResources Table. I write the below code for getting the the monthwise and division wise closing head count.. How many employees are there at the end of that month. DiviSeqMonthClosingCount = VAR CurrentMonth = SELECTEDVALUE (DateTable [MonthNo]) VAR CurrentYear = SELECTEDVALUE (DateTable[Year]) VAR EOMonthNo = Day (EOMONTH (Date (CurrentYear, CurrentMonth,1),0)) Return CALCULATE (DISTINCTCOUNT (A11Resources [Corp ID]), AllResources [OnboardingDateTime] <= date (CurrentYear, CurrentMonth, EOMonthNo) && ( (A11Resources [Offboarding End Date] > date (CurrentYear, CurrentMonth, EOMonthNo)) || ISBLANK (A11Resources [Offboarding End Date])), ALL (DateTable), USERELATIONSHIP (AllResources [OnboardingDateTime], DateTable[Date])) I am getting the below output. Below the DiviMonthClosingcount needs to repeat the count for other months also like Opening Count measure. Division MonthYear Opening Count DiviMonthClosingcount Governance Jan'25 17 17 Governance Feb'25 17 Governance Mar'25 17 Governance 5 Apr 25 17 Governance May 25 17 MCM Jan 25 68 71 MCM Feb 25 68 MEM 25-Mar 68 71 MCM Apr"25 68 MCM May 25 68 70 I need the below output for closing count. Division MonthYear Opening Count DiviMonthClosingcount Governance Jan'25 17 17 Governance Feb'25 17 17 Governance Mar'25 17 17 Governance 5 Apr 25 17 17 Governance May 25 17 17 MCM Jan 25 68 71 MCM Feb 25 68 71 MEM 25-Mar 68 71 MCM Apr"25 68 71 MCM May 25 68 70 I am unable to find out the missing logic as I tried with Filter function also and even I put the ALL(Allresources). I am not getting the desired result. Please help me out on this. I think some where I missed the filter or someting in the logic. The relationship between the tables also explained above. Thanks DathySolved1KViews0likes5Comments