Correct the DAX measure closing count DAX measure

[SriBhaskar]

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

Dathy

 

[Anonymous]

Hi @SriBhaskar ,

Please try:

MEASURE = 
VAR max_date = MAX ( 'DateTable'[Date] )
RETURN
CALCULATE (
    COUNTROWS ( 'AllResources' ),
    'AllResources'[Onboarding Date] <= max_date,
    'AllResources'[Offboarding End Date] >= max_date 
        || ISBLANK ( 'AllResources'[Offboarding End Date] ),
    CROSSFILTER ( 'AllResources'[Offboarding End Date], 'DateTable'[Date], NONE )
)

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[sjoerdvn · Accepted solution]

I've tested this with the data and measure you provided. Apart from having to correct quite some column names in there it actually worked fine. It would only fail if the cross filter direction of the relationships with the date table are set to "Both". 

[SriBhaskar]

I already tried this approach but I am not getting the desired output as mentioned above. Any other approach you have please let me know

[SriBhaskar]

This I already tried.I am not getting the desired output.

[bhanu_gautam]

@SriBhaskar , Try using

DAX
DiviSeqMonthClosingCount =
VAR CurrentMonth = SELECTEDVALUE(DateTable[MonthNo])
VAR CurrentYear = SELECTEDVALUE(DateTable[Year])
VAR EOMonthDate = EOMONTH(DATE(CurrentYear, CurrentMonth, 1), 0)
RETURN
CALCULATE(
DISTINCTCOUNT(AllResources[Corp ID]),
FILTER(
AllResources,
AllResources[Onboarding Date] <= EOMonthDate &&
(AllResources[Offboarding End Date] > EOMonthDate || ISBLANK(AllResources[Offboarding End Date]))
),
ALL(DateTable),
USERELATIONSHIP(AllResources[Onboarding Date], DateTable[Date])
)