TOTALYTD but dont accumulate certain rows

[ixtilion]

Hello everyone,

I'm having a tough time with this one, as I think I need to simulate TOTALYTD some other way as to change its behaviour.

We have a set of 4 employees, which we are counting for the amount of days they were active in the current month. We must accumulate this value through the year, but the moment one of them is a Leave or a Hire in one month, we must show "0" as its accumulated value in subsequent months, while still accumulating the count of the other employees from previous months.

Right now Im able to stop counting these values with no issue in subsequent months (column D), the problem comes when I try to accumulate the value (column E).

I highlighted in yellow the values that should be a 0 in column E, as they were a Leave in 201806 (highlighted the Leave in column G) and the count of 31 from 201805 should stop being accumulated from 201806 onwards.

I tried to make this accumulated measure with a CALCULATE(TOTALYTD([COUNT];DATE[DATE];"1/31");FILTER(EMPLOYEES WITHOUT HIRE/LEAVE IN PREVIOUS MONTHS)) in order to exclude values that shouldnt be counted in the current month, but it doesnt seem to accumulate the other employees properly.

¿Is there a way to do this? I've tried some other stuff but no luck, It either accumulates everything, or doesnt accumulate anything at all, as it gets filtered to the current month we are evaluating.

If I can explain myself better, just let me know and I'll try to provide more insight or examples.

Im completely lost about how to do this with DAX, is it even possible?

Thanks!

DATA SAMPLE USED:

YEAR	MONTH	EMPLOYEE ID	COUNT	CUMULATIVE COUNT	HIRE	LEAVE
2018
	5		93	93	1
		1226524	31	31
		1257491	31	31
		1432973	0	0	1
		1584960	31	31
	6		60	153		1
		1226524	30	61
		1257491	30	61
		1432973	0	0
		1584960	0	31		1
	7		62	215
		1226524	31	92
		1257491	31	92
		1432973	0	0
		1584960	0	31

[Greg_Deckler]

You might find Time Intelligence the Hard Way useful: https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

[ixtilion]

Thanks Greg,

I was able to replicate TOTALYTD behaviour with your solutions, but Im still unable to, come June and July, exclude the count from may.

TEST_HW:=
VAR MaxYear = MAX('DATE'[YEAR])
VAR MaxMonth = MAX('DATE'[MONTH_ORDER])
VAR TmpTable = CALCULATETABLE('HEAD_COUNT';ALL(DATE))

RETURN SUMX(FILTER(TmpTable;HEAD_COUNT[YEAR]=MaxYear && HEAD_COUNT[MONTH] <= MaxMonth);IF([HIRED_OR_LEFT_CURR_MONTH]=1;0;[ACCUMULATED COUNT]))

Im able to not add the count data for June and July, but Im still dragging the count of 31 from May (BAJAS_EMPRESA being LEAVES in the screenshot) the same way It happened with TOTALYTD:

I cant find a meaningful way to modify this inside the formula in order to make it count the 31 days of May when we are evaluating May, and not take them into account while evaluating June and July and accumulating the measure 😞

Thanks anyways, always good to see I can calculate time functions by myself without relying on preset ones