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

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes! 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

Share with Power BI Enthusiasts: 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
September Power BI Update Carousel

Power BI Monthly Update - September 2025

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

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

Top Solution Authors
Top Kudoed Authors