How to make cumulative totals with changing parameters during time

[ngrulovic]

Dear Friends,

I am trying to make cumulative totals of HR database where the employees during the time change their grades, salaries and cost center and this should be reflected in the cumulative totals over time but still count as one employee. For example for employee 100044 I should have it in cumulative total as 1 and not 5 but I for example if I want to present Cost center headcount breakdown this particular employees  will be until 01 April 2018 on CC-2 but after this date it should be on CC-16 and not double.

I have attached the file https://drive.google.com/file/d/1w2yhvbOX2FbMmoHa-21UkEdLd2XqYS23/view?usp=sharing 

Thank you very much in advance.

[ngrulovic]

Dear Alisson,

This is it! Thank you very much!

[AllisonKennedy]

You're welcome!

[AllisonKennedy · Accepted solution]

@ngrulovic Thanks for the sample file. 

I used method described here: https://excelwithallison.blogspot.com/2020/06/dax-approximate-lookup.html 

And I also replaced your Date table with one that has Month number for correct sorting: https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html 

If I understand correctly, I think this is what you're after:

I created a calculated column: 

Next Change Date =

VAR _EmpID = Database[Employee ID]

VAR _ChangeDate = Database[Change Date]

RETURN

MINX(FILTER(Database, Database[Change Date] > _ChangeDate && Database[Employee ID] = _EmpID), Database[Change Date])

and a measure:

Cumulative Total Head Count =

VAR _DateContext = MAX(Dates[Date])

RETURN

CALCULATE(

DISTINCTCOUNT( Database[Employee ID])

, FILTER(Database,

VAR _ChangeDate = Database[Change Date]

VAR _NextChangeDate =

VAR _EmpID = Database[Employee ID]

RETURN

MINX(FILTER(Database, Database[Change Date] > _ChangeDate && Database[Employee ID] = _EmpID && Database[Change Date] <=_DateContext), Database[Change Date])

RETURN

Database[Change Date] <= _DateContext

&& _NextChangeDate = BLANK()

&& (Database[Next Change Date] > _DateContext || Database[Next Change Date] = BLANK())

))

Then you can see Cumulative Head Count gradually increase, as Cost Center distribution changes (but doesn't double count anyone): 

For your example Employee ID = 100044: 

See sample file attached below signature.