Get value if between dates

[Anonymous]

Good day,

I gave quite a complex problem (I think).

I have a large dataset with Leads.

Every Lead has a unique LeadId. Every lead is in a phase ranging from 1 to 7, LeadPhaseId. For every Lead is registered when it has been created, CreatedDate.

The fun part:

This table only consists of current data, meaning the LeadPhase represents the CURRENT state of the lead.

To solve this, I have a second table with 'history' data, showing lead phases from when to when they where in that state. (LeadPhaseTransition)

My problem:

I have made a visual showing the number of leads in a certain state at a certain date using CreatedDate. However, this data is not representative at all, because my visual only looks at the current data (current phase) and not the actual phase it was in at that date.

Example:

[Anonymous]

Hi @Anonymous ,

I'm not sure what you want. What's the relationship between two tables? You are using create date as date between slicer? Please show more details.

Best Regards,

Jay

[amitchandak]

@Anonymous , not very clear. See if this can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[Anonymous]

I've looked at your .pbix file and it is essentially the solution I was looking for. Only do I have one problem. To make it easier to understand my problem I will use your dataset from now on.

For example: Employee with name N gets hired again on April 14th 2019. A new entry will be created with EmployeeId 20. His start date will be April 14th 2019, and end date will be NULL.

Using this in a graph employee N is counted twice in April 2019. How exactly would I solve that?