Forum Discussion
Measuring pipeline over time
Hello experts, hoping someone can help me. I'd like to be able to create a chart that shows the pipelines total value over time.
For example, using the data below I woul expect...
January 31 = 200 (Opp1+Opp2)
February 29= 300 (Opp2+Opp3+Opp4)
March 31= 300 (Opp3+Opp4+Opp5)
April 30= 200 (Opp4+Opp5)
May 31= 100 (Opp5 since it hasn't closed yet)
| ID | Value | Created Date | Closed Date |
| opp1 | 100 | 1-Jan | 10-Feb |
| opp2 | 100 | 15-Jan | 10-Mar |
| opp3 | 100 | 1-Feb | 10-Apr |
| opp4 | 100 | 15-Feb | 10-May |
| opp5 | 100 | 1-Mar |
Thanks in advanced!
10 Replies
Anonymous ,
Could you please clarify the logic why February 29= 200 (Opp2+Opp3), not February 29= 300 (Opp2+Opp3+Opp4)?
Regards,
Jimmy Tao
Playing with this a little bit more I seem to have found a solution, but it's very slow and I am sure not the right way to go about this.
What I did was create a measure for pipeline cumulative total by created date and another measure for pipeline cumultive total by closed date. Then I created another measure that subtracts the measures. It seems to work, but is pretty slow to calculate, so I am assumig it's not the "right" way to go about this.
Anonymous ,
Suppose the date is selected from another table, you can create a measure using dax below:
Result = CALCULATE(SUM('Table'[Value]), FILTER('Table', 'Table'[Created Date] <= SELECTEDVALUE('Date'[Date]) && ('Table'[Closed Date] >= SELECTEDVALUE('Date'[Date]) || 'Table'[Closed Date] = BLANK())))Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
I am not sure whom you are replying to. Have you tried my solution?
