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(
ADDCOLUMNS(
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(
ADDCOLUMNS(
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(
ADDCOLUMNS(
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 -
So now, I’m not sure about this statement that you’ve specified in your original post -
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)
