Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
Anonymous
Not applicable

Get value if between dates

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.

werner1107_2-1597314434154.png

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)

werner1107_1-1597314091870.png

 

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:

Aantekening 2020-08-13 123622.png

3 REPLIES 3
Anonymous
Not applicable

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
Super User
Super User

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

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Anonymous
Not applicable

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?

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.