# Calculate the total amount of Lost revenue projects

Hi All,

I have a number of projects running through different months specifically in this scenario October and November and I want to compare the projects in the current month November to the projects in the previous month that of description column - “Step I” which are no longer in the pipeline i.e they are lost. They were in the pipeline i.e Step I in October but are no longer Step I in November.

I have tried a version of Sam’s Mckay formula
Lost Projects =
VAR CurrentProjects = CALCULATETABLE(
VALUES(PipelineTrackerData[Project Codes]),
PipelineTrackerData[Step] = “I”)
VAR PriorProjects = CALCULATETABLE(
VALUES(PipelineTrackerData[Project Codes]),
FILTER(
ALL(FYCalendar),
FYCalendar[Date]> MIN(FYCalendar[Date]) - 30 &&
FYCalendar[Date]< MIN(FYCalendar[Date])
),
PipelineTrackerData[Step] = “I”)
Return
CALCULATE(
SUMX(
EXCEPT(PriorProjects,CurrentProjects),
[Revenue (-1)])

And I have about 5.2 million in lost revenue. But now here is where it gets tricky. These projects that are considered lost I want to now assume the description of these projects in the current month i.e November. I want a way to apply the current description in November to these lost projects so I can filter out only are those projects that are of description column “not” won. So the correct amount I am looking for is 4.73 million.

Here is the data to work from and your help will be greatly appreciated. If you need further explanation please let me know. Thanks

Lost Projects.xlsx (25.0 KB)

Hello @ambepat,

Thank You for posting your query onto the Forum.

To achieve the results based on the conditions that you’ve specified and also based on the formula logic that you’ve written in your original post -

1). I have a number of projects running through different months specifically in this scenario October and November and I want to compare the projects in the current month November to the projects in the previous month that of description column - “Step I” which are no longer in the pipeline i.e they are lost.

2). They were in the pipeline i.e Step I in October but are no longer Step I in November.

3). These projects that are considered lost I want to now assume the description of these projects in the current month i.e November.

4). Calculate the total amount of Lost revenue projects.

So now, based on these conditions i.e. Step I available in October but no longer available in the month of November and therefore those Project Numbers should be reflected in the month of November in order to analyze the lost revenue. Below are the measures alongwith the screenshot of final results provided for the reference -

1). Measure For Total Projects -

``````Total Projects =
COUNTROWS( Data )
``````

2). Measure For Total Revenue -

``````Total Revenue =
SUM( Data[Revenue (£)] )
``````

3). Measure for Lost Project Date i.e. Time Period when Project is considered as Lost -

``````Date Lost Project =
CALCULATE(
EOMONTH( MAX( Data[End of Month] ) , 1 ) ,
REMOVEFILTERS( Dates ) )
``````

4). Measure for No. of Lost Projects i.e. Comparing Current Month With The Previous Month -

``````No. of Lost Projects =
VAR _Last_Date_Lost =
CALCULATE( MAX( Dates[Date] ) ,
ALLSELECTED ( Dates ) )

VAR _Projects_With_Lost_Date =
CALCULATETABLE(
VALUES( Data[Project Code Number] ) ,
"@LostCustomerDate" ,
[Date Lost Project] ) ,
ALLSELECTED( 'Project Code Number' ) ,
Dates[Date] <= _Last_Date_Lost )

VAR _Lost_Projects =
FILTER(
_Projects_With_Lost_Date ,
[@LostCustomerDate] IN VALUES ( Dates[Date] ) )

VAR _Results =
COUNTROWS ( _Lost_Projects )

RETURN
_Results
``````

5). Measure for Lost Project Names i.e. Names or Project Numbers that are considered as Lost -

``````Lost Project - Names =
VAR _Last_Date_Lost =
CALCULATE( MAX( Dates[Date] ) ,
ALLSELECTED( Dates ) )

VAR _Projects_With_Lost_Date =
CALCULATETABLE(
VALUES( 'Project Code Number'[Project Code Number] ) ,
"@LostCustomerDate" ,
[Date Lost Project] ) ,
ALLSELECTED ( 'Project Code Number' ) ,
Dates[Date] <= _Last_Date_Lost )

VAR _Lost_Projects =
FILTER(
_Projects_With_Lost_Date ,
[@LostCustomerDate] IN VALUES ( Dates[Date] ) )

VAR _No_Of_Lost_Projects =
COUNTROWS ( _Lost_Projects )

VAR _Names_Of_Projects_Lost =
CONCATENATEX(
_Lost_Projects ,
'Project Code Number'[Project Code Number] ,
", " )

VAR _Results =
IF( HASONEVALUE( Dates[Month & Year] ) ,
_Names_Of_Projects_Lost ,
BLANK() )

RETURN
_Results
``````

6). Measure for Lost Projects Revenue i.e. How Much Revenue is Lost against those Projects -

``````Lost Projects Revenue =
VAR _Last_Date_Lost =
CALCULATE( MAX( Dates[Date] ) ,
ALLSELECTED ( Dates ) )

VAR _Projects_With_Lost_Date =
CALCULATETABLE(
VALUES( Data[Project Code Number] ) ,
"@LostCustomerDate" ,
[Lost Project Date] ) ,
ALLSELECTED ( 'Project Code Number' ) ,
Dates[Date] <= _Last_Date_Lost )

VAR _Lost_Projects =
FILTER(
_Projects_With_Lost_Date ,
[@LostCustomerDate] IN VALUES ( Dates[Date] ) )

VAR _Previous_1_Month =
DATESINPERIOD(
Dates[Date] ,
EOMONTH ( MAX ( Dates[Date] ) , -1 ) ,
-1 ,
MONTH )

VAR _Results =
CALCULATE( [Total Revenue] ,
_Previous_1_Month ,
KEEPFILTERS ( _Lost_Projects ) )

RETURN
_Results
``````

Now, after writing all these measure I cross-verified these results with the Excel file and they’re a “Perfect Match”. Below is the screenshot of the cross-verification of results provided for the reference -

1). I have about 5.2 million in lost revenue.

2). So the correct amount I am looking for is 4.73 million.

None, of your statements were matching with the results evaluated based on the conditions that you’ve actually specified and the data which you’ve provided in your orginal post as the requirements.

I’m also attaching the working of the Excel as well as PBIX file for the reference purposes.

Hoping you find this useful and meets your requirements that you’ve looking for.

Important Note’s:

1). With regards to this aspect - "I want a way to apply the current description in November to these lost projects so I can filter out only are those projects that are of description column “not” won."

I haven’t modified the data model and have considered as is based on the data that was provided, and therefore that aspect I leave it upto you. Although, I’ve already provided the names of the Project Number’s that’re considered as Lost which you can already see in the screenshot of the Final Results. So with regards to additional requirements, you can modify the model and write the additional measures as per your business scenario’s and requirements since heavy lifting is already done by the above provided measures.

2). Going on, also please attach the working PBIX files as well to showcase what progress was actually made and the results that you’re expecting in those files or in the Excel mock-up file. It consumes well amount of time for the members to create everything from scratch and then provide the solutions by cross-verfying the numbers.

3). Lastly, we as an Experts request to the members of the Forum not to mark or loop individuals specifically or call out them i.e. either to specific Forum Members or specific Expert Members. When this is done, it refrains the other members or experts from answering the questions who might be able to answer them in the first instance.

Thanks and Warm Regards,
Harsh

Lost Projects Data - Harsh.xlsx (49.2 KB)

Lost Revenue Projects - Harsh.pbix (70.8 KB)

1 Like

@Harsh Awesome thanks for this detailed workings and I take on board your comments on tagging experts. Will reframe from doing so in the future. I will analyse your solution and come back if I have any questions but really appreciate the effort you put in. Thanks.

@Harsh Thanks for your solution but I think what you provided has part of the answer but not all. What I have done is included a PBIX of my workings to show you what I am after and also an excel to show you the checks/results I am looking for. I have also change the project codes to give you an accurate reference for the numbers I am looking to calculate

So what is the requirement

1. Look for all projects in the previous month that of Step I that are no longer Step I in the current month. So if you look at the excel sheet I provided you will see that the projects I calculated using sam’s formula are of Step I in October and that comes down to 5,266,462.69 and if you look at those same projects that are of Step I in October you will see they are no longer Step I in November. So projects have dropped off meaning they are either N/A error and other projects have a different Step which is not I i.e they are either completed, decided not to pursue, Lost etc.

2. Now the second requirement for us to accurately determine what amount of projects that were indeed dropped if you look at the description in November in the excel file you will see that I only want those projects that are NOT “Completed” or “Confirmed” and you will see it give you the amount I am looking for 4,737,875.

I hope this a lot clearer and I have included a PBIX to show my own workings.

Thanks a lot in advance for you help again.

Pipeline Tracker V3.pbix (252.1 KB)
Lost Projects Checks.xlsx (180.2 KB)

Hello @ambepat,

Now, this is more helpful when we receive the complete scenario alongwith the working of the PBIX files and the results that you’re expecting or trying to achieve.

So based on the scenario that you’ve suggested, again the logic remains absolutely same to evaluate the initial results but only the final lines of the measures gets modified where we need to specify what you actually don’t require i.e. NOTCompleted” or “Confirmed”. Below are the measures alongwith the screenshot of the final results provided for the reference -

``````No. of Lost Projects - Harsh =
IF( ISBLANK( [Lost Projects Revenue - Harsh] ) ,
BLANK() ,
CALCULATE(
VAR _Last_Date_Lost =
CALCULATE( MAX( FYCalendar[Date] ) ,
ALLSELECTED ( FYCalendar ) )

VAR _Projects_With_Lost_Date =
CALCULATETABLE(
VALUES( PipelineTracker[Project Codes] ) ,
"@LostCustomerDate" ,
[Lost Project Date - Harsh] ) ,
ALLSELECTED( PipelineTracker ) ,
FYCalendar[Date] <= _Last_Date_Lost )

VAR _Lost_Projects =
FILTER(
_Projects_With_Lost_Date ,
[@LostCustomerDate] IN VALUES ( FYCalendar[Date] ) )

VAR _Results =
COUNTROWS ( _Lost_Projects )

RETURN
_Results ,
PipelineTracker[Description] <> "Decided not to pursue" ,
PipelineTracker[Description] <> "Opportunity not proceeding" ,
PipelineTracker[Description] <> "Lost" ) )

Lost Projects Revenue - Harsh =
CALCULATE(
VAR _Last_Date_Lost =
CALCULATE( MAX( FYCalendar[Date] ) ,
ALLSELECTED ( FYCalendar ) )

VAR _Projects_With_Lost_Date =
CALCULATETABLE(
VALUES( PipelineTracker[Project Codes] ) ,
"@LostCustomerDate" ,
[Lost Project Date - Harsh] ) ,
ALLSELECTED ( PipelineTracker ) ,
FYCalendar[Date] <= _Last_Date_Lost )

VAR _Lost_Projects =
FILTER(
_Projects_With_Lost_Date ,
[@LostCustomerDate] IN VALUES ( FYCalendar[Date] ) )

VAR _Previous_1_Month =
DATESINPERIOD(
FYCalendar[Date] ,
EOMONTH ( MAX ( FYCalendar[Date] ) , -1 ) ,
-1 ,
MONTH )

VAR _Results =
CALCULATE( SUM( PipelineTracker[Revenue (£)] ) ,
_Previous_1_Month ,
KEEPFILTERS ( _Lost_Projects ) )

RETURN
_Results ,
PipelineTracker[Description] <> "Decided not to pursue" ,
PipelineTracker[Description] <> "Opportunity not proceeding" ,
PipelineTracker[Description] <> "Lost" )
``````

Also the numbers are cross-verified with the scenario that you had presented. Below is the screenshot of that scenario provided for the reference as well -

I’m also attaching the working of the Excel as well as PBIX file for the reference purposes.

Hoping you find this useful and meets your requirements that you’ve been looking for.

Thanks and Warm Regards,
Harsh

Lost Projects Checks - Harsh.xlsx (180.0 KB)

Pipeline Tracker V3 - Harsh.pbix (218.4 KB)

HI @Harsh I am struggling to understand this bit of the DAX

VAR _Projects_With_Lost_Date =
CALCULATETABLE(
VALUES( PipelineTracker[Project Codes] ) ,
@LostCustomerDate” ,
[Lost Project Date - Harsh] ) ,
ALLSELECTED( PipelineTracker ) ,
FYCalendar[Date] <= _Last_Date_Lost )

Under the current filter context for date, the last date lost is 30/11/2021. Now the @LostCustomerDate not sure why that is included as that is 31/12/2021. I am confused or can’t interpret what is happening from a filter context perspective. Can you please just expand what is happening here we

``````VAR _Lost_Projects =
FILTER(
_Projects_With_Lost_Date ,
[@LostCustomerDate] IN VALUES ( FYCalendar[Date] ) )
``````

What dates is the project filtering. I understand VALUES ( FYCalendar[Date] ) will be 30/11/2021.

Yeah very challenging for me to see what is happening. Your expansion will be appreciated.

Hello @ambepat,

Below is the explanation provided against each line of code -

``````Lost Projects Revenue - Harsh =
CALCULATE(
VAR _Last_Date_Lost =
CALCULATE( MAX( FYCalendar[Date] ) ,                    -- Calculates the Max or Last Date of the Project Number
ALLSELECTED ( FYCalendar ) )                        -- Retaining the context of the Calendar i.e. Month & Year slicer selected in this case

VAR _Projects_With_Lost_Date =
CALCULATETABLE(                                         -- Prepares a virtual table that
VALUES( PipelineTracker[Project Codes] ) ,      -- for each Project Code containing
"@LostCustomerDate" ,                           -- a virtual column with
[Lost Project Date - Harsh] ) ,                 -- the date when they are considered lost
ALLSELECTED ( PipelineTracker ) ,                   -- regardless of local filters on PipelineTracker
FYCalendar[Date] <= _Last_Date_Lost )           -- and on Date

VAR _Lost_Projects =
FILTER(
_Projects_With_Lost_Date ,                          -- Filters the Project Codes
[@LostCustomerDate]                             -- whose Lost Customer Date
IN VALUES ( FYCalendar[Date] ) )            -- falls within the current time period

VAR _Previous_1_Month =
DATESINPERIOD(
FYCalendar[Date] ,                                  -- Retrieve the previous 1 month
EOMONTH ( MAX ( FYCalendar[Date] ) , -1 ) ,         -- to evaluate the loss in revenue
-1 ,
MONTH )

VAR _Results =
CALCULATE( SUM( PipelineTracker[Revenue (£)] ) ,        -- Calculates the Revenue lost
_Previous_1_Month ,                                 -- in last 1 month
KEEPFILTERS ( _Lost_Projects ) )                    -- where 30/11/2021 from the virtual table matches
-- with the 30/11/2021 from the date table and results are evaluated

RETURN
_Results ,
PipelineTracker[Description] <> "Decided not to pursue" ,
PipelineTracker[Description] <> "Opportunity not proceeding" ,
PipelineTracker[Description] <> "Lost" )
``````

I’m also providing the link of the DAX Patterns below. Please go through that link for more understanding of the concepts.

Thanks and Warm Regards,
Harsh

https://www.daxpatterns.com/new-and-returning-customers/#

Thanks @Harsh