cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
frankvb
Helper I
Helper I

Snapshot table in a measure

I have to get a snapshot (the ammount of values in the pipeline), but i dont have a date to use.
This is a simplified version of the data:

Projectdate_requestdate_decisionDecisiondate_closed
project A01/01/200201/05/2002Approved02/05/2002
project B04/01/200307/06/2002Approved 
project C31/01/200201/03/2004Denied01/03/2004
project D15/05/2006 Undecided 
project E09/02/2007 Undecided 

 

 

It is easy enough to show what is in the pipeline right now.

I just have to filter on decision and closing date.

But also need a snapshot at the end of each month showing how many projects where outstanding at that point.

So I need to be able to say at the end of januari 2002: there were 2 outstanding projects (A and C).
This seems quite complex.

What I am also have a hard time with, is to use this table as a timeline in a visual. Which one to choose.

Anybody else encountered a similar problem?

I tried with this, but it does not make much sense: 

Pipeline  =
 
VAR X = ENDOFMONTH(date_request)

VAR ThenInPipeline = calculate(
distinctcount([Project]),
[date_request] < X,
[date_decision] > X)
 
RETURN
ThenInPipeline
1 ACCEPTED SOLUTION
amitchandak
Super User IV
Super User IV

@frankvb , refer if this blog on HR can help you - https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

 

It deal with 2 dates and Active(Current employee), a Very similar approach. - https://www.youtube.com/watch?v=e6Y-l_JtCq4



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

2 REPLIES 2
frankvb
Helper I
Helper I

@amitchandak thanks once again!

amitchandak
Super User IV
Super User IV

@frankvb , refer if this blog on HR can help you - https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

 

It deal with 2 dates and Active(Current employee), a Very similar approach. - https://www.youtube.com/watch?v=e6Y-l_JtCq4



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors