Quarter over Quarter Pipeline Shifts

[Anonymous]

I am making a quarter over quarter report and am struggling with the final piece: showing items that were in the quarterly pipeline at the start but are no longer in it at the end.

Project ID	Revenue	Close Date	Report Date
12345	500	6/5/2020	6/5/2020
45678	100	6/5/2020	6/5/2020
12345	5000	6/12/2020	6/12/2020
45678	100	6/5/2020	6/12/2020
12345	600	6/12/2020	6/19/2020
45678	100	10/2/2020	6/19/2020
12345	500	7/8/2020	6/26/2020
45678	200	6/5/2020	6/26/2020

In  the example above, 2 projects are being tracked. At the start of the reporting period, both projects are set to close within the quarter, so both are in the pipeline. By the end of the reporting period, 12345 is now scheduled to close next quarter, and 45678 has had a change to its revenue. I need to track those changes, and I am able to track total revenue at start compared to at end. I am having trouble identifying projects that were in the pipeline but no longer are. I have been using calculated columns to set flags, but I am having great difficulty comparing the flag at a given date to a flag later down the line. Additionally, for some reason my report date column isn't identified as a date hierarchy despite having all the elements necessary. Any help is appreciated; I am very puzzled

[v-xicai]

Hi @Anonymous ,

I am not sure what desired result would you want, could you please share your sample data and desired output screenshots for further analysis? You can also upload sample pbix to OneDrive and post the link here. Do mask sensitive data before uploading.

Please read this post to get your answer quickly: How to Get Your Question Answered Quickly.

Best Regards,

Amy 

[Anonymous]

@v-xicai @Ashish_Mathur sorry for the confusion in my question. I need to show shift from start of quarter to end of quarter. Specifically, I need to show:

• how many projects were in the pipeline at the start of the quarter (in this case, 2, because, at the start of the reporting period) both of them are set to close at the end of the quarter)
• how much revenue was in the pipeline at the start of the quarter ($600 from the 2 projects)
• which, if any projects, got pushed out of the pipeline (12345 in this case, because the close date was moved to Q3)
• the revenue in the pipeline at the end of the quarter ($200, from 45678)

I am able to do all but the tracking of which projects got pushed out. I have been working on this for a week now, so everything I need is very clear in my mind, but let me know if I am flubbing the explanation. I can always provide more details.

[Ashish_Mathur]

Hi,

I still cannot visualise the end result that you are expecting.

[Anonymous]

Fair enough. I run multiple reports over the quarter, so my starting report would look like this:

Project ID	Revenue	Close Date	Report Date	In Quarter at Start?	In Quarter at End?
12345	500	6/15/2020	6/5/2020	1	0
45678	100	6/15/2020	6/5/2020	1	0

In the first report I run (6/5/2020), both opportunities are set to close in Q2 (6/15/2020), so I flag them as such using the calculated column In Quarter at Start? and assign them 1. In Quarter at End? is another calculated column, but since it isn't end of quarter, both projects are assigned 0 (when there is only 1 report date, they are actually assigned 1 because STARTOFQUARTER(report_date) is 6/5/2020 and ENDOFQUARTER(report_date) is 6/5/2020 as there is only 1 report date).

Now, at the end of quarter, I run another report:

Project ID	Revenue	Close Date	Report Date	In Quarter at Start?	In Quarter at End?
12345	500	7/8/2020	6/26/2020	0	0
45678	200	6/15/2020	6/26/2020	0	1

There are now at least two report dates, so STARTOFQUARTER is 6/5/2020 and ENDOFQUARTER is 6/26/2020. Unfortunately, since the reports are all appended together in a master table, the rows will have different column values for In Quarter at Start? and In Quarter at End?. What I want to be able to do efficiently is look at ID 12345 and see that at the beginning of the reporting period it had a 1 for In Quarter at Start? and then see that it now has a 0 for In Quarter at End? so that I can count it as being pushed to next quarter. I currently have been able to do this using:

q2_start_value = 
VAR _oid = calculate(selectedvalue(master[Project ID]))
VAR _endVal = calculate(SUM(master[In Quarter at Start?]),filter(master,master[Project ID]=_oid))
RETURN
IF(_endVal>=1, 1, 0)

This doesnt seem particularly efficient as it is just pulling the start value to every single row that matches product ID (since I am working with 6 different report dates, I have over 3000 rows (and this will keep growing as more reports are run). What I would like, is to be able to just put the start value at the maximum report date, but I am unsure of how to do this. Any suggestions?

[Ashish_Mathur]

Hi,

You say that for Project ID 45678, the revenue changed.  Why do you not ay that for Project ID 12345.  Clearly show the end result that you are expecting.