Determine First/Last In Period

[BM4291]

Hi,

Having some difficulty with a measure; I've previously had some help with setting up a slowly changing dimension measure which allows me to see whether people are on record during a period of time and have now used this extensively in a number of projects, works very well.

The issue I'm having now concerns trying to pull meaningful demographic data as I can't determine the earliest/last instance in a period for a person... Hopefully that makes sense, see below where I've highlighted the records (fake data unfortunately):

When I try to group by age, my totals are >100% because it is possible for someone to be in multiple ages in the defined period i.e. in the 2019-20 financial year both John Smith and John Doe appear multiple times, with John Doe having a birthday between jobs. If I use the below measure everything is fine until I want to see this at a more granular level such as Age.

people = DISTINCTCOUNT([ID])
start = DATE(2019,04,01)
end = DATE(2020,03,31)

peopleInPeriod = CALCULATE([people]
	, FILTER([PeopleJobs],
		[JobIssue] <= [end]
		&& ([JobComplete] >= [Start] || ISBLANK([JobComplete]))
		&& [CaseReceipt] <= [end]
		&& ([CaseClosure] >= [start] || ISBLANK([CaseClosure]))
)

If I look at this by Age I will get 1 entry for 30, 1 for 39 and 1 for 40 (3) and a total of 2 which when divided gives me 150%...

What I need is to dynamically pick out the earliest or latest (I can decide which works best for the business, likely latest) job to say how old a person was in that period rather than returning the two values.

I have tried using a calculated table but I can't make this responsive to slicers and not sure it's the best way around this.

Any thoughts?

[v-xicai]

Hi @BM4291 ,

I am not sure what desired result would you want, could you please share your sample data and desired output screenshots for further analysis? You can also upload sample pbix to OneDrive and post the link here. Do mask sensitive data before uploading.

Please read this post to get your answer quickly: How to Get Your Question Answered Quickly.

Best Regards,

Amy

[BM4291]

Hi @v-xicai ,

I thought this was quite clear in the original message but please see a mock-up table below based on the scenario I've described above focusing specifcally on the John Doe record:

Age	Current Result	Desired Result
39	1
40	1	1
Total	1	1

Currently I see a result for John Doe under 39 and 40 when I only want to see the result of his latest record which makes him 40; records are ordered based on the JobIssue date in the table in the original post.

Using the measures etc that I currently have I'm looking at all jobs whereas I need the measure to dynamically pick the earliest/latest in the period; as I mentioned, it's most likely that I'd use latest to distinguish the two in the business application.

[BM4291]

Just bumping this as still struggling with getting this one to work; as in the original question I can pick out all people in a specific period using a slowly changing dimension but am struggling to pick out a specific record of theirs to avoid duplicate counts when I start to break the data down into categories.