Semi Additive Measure as a Master Measure

[ghaliabed]

Hello

I am trying to figure out how to work with semi additive measures.

Have requirement to track the Number of Employees per entity and team over time.

I have in my data down to the level of the Employee and if they are present will be flagged

Sample Data:

YEAR	Quarter	YEARMONTH	ENTITY	TEAM	EMPLOYEE	EMPLOYEECOUNT
2019	1	201901	A	DEV	JOE	1
2019	1	201902	A	DEV	JOE	1
2019	1	201903	A	DEV	JOE	1
2019	2	201904	A	DEV	JOE	1
2019	2	201905	A	DEV	JOE	1
2019	1	201901	A	DEV	MIKE	1
2019	1	201902	A	DEV	MIKE	1
2019	1	201903	A	DEV	MIKE	1
2019	2	201905	A	DEV	MIKE	1
2019	1	201901	A	QA	SAM	1
2019	1	201902	A	QA	SAM	1
2019	1	201903	A	QA	SAM	1
2019	2	201904	A	QA	SAM	1

I want to have a master measure where users can create pivot tables and charts and be able to correctly see the latest Number of Present Employees based on their chosen dimensions.

Now if a user is always grouping the Sum(EmployeeCount) at the YEARMONTH level, they will get the correct value.

Ex: Pivot Table By Entity over YEARMONTH will give results like this

However, if user is doing any grouping without the YEARMONTH they will get incorrect results.

Ex: Pivot Table By Entity and Team over Quarters

The values here are wrong at Quarter level, where the correct values should be:

Q1: Show 3 instead of 9

Q2: Show 2 instead of 4

Is there a way to do this as a set expression and make it as a Master Measure?

[ghaliabed]

any ideas on this ?

[Saravanan_Desingh]

Are you looking something like this?

[ghaliabed]

Not exactly no.

If we remove the Month 201905 from pivot table, the Total for the DEV would still be 2.

But the correct value should actually be 1, since that is the value found in the Last available month