Retrieve most recent value based on the closest start date

ysherriff · September 2, 2022, 2:42pm

I was given a formula to extract the results based on the minimum date for each group of “ **BC # thanks to Harsh, which helped in a different analysis.

But I am looking for a formula that would extract the most recent Campaign ID that is the closest to RFP Received Date based on Campaign start date.

As an example in image 1, for BC# 118224-01, the campaign that would be shown in the table would be EWCUIDA05.

Image #1

I found this formula online and thought it would help image #2, but it is not the expected result, see image #3.

Image #2

Image #3.

Where am I going wrong and

1… How would do the logic for the 2nd closet date.

I will see if I can also find an alternative solution by using Harsh previous syntax.

WIP-EDNA.pbix (21.9 MB)

Thank you for your help.

Harsh · September 3, 2022, 9:29am

Hello @ysherriff,

Thank You for posting your query onto the Forum.

In order to achieve the results of the most recent value of “Campaign ID” based on last occurred “Campaign Start Date”. Below is the measure alongwith the screenshot of the final results provided for the reference -

Most Recent Campaign ID - Harsh =
VAR _Current_BC =
SELECTEDVALUE( Campaigns[BC #] )

VAR _Max_Date =
MAXX(
    TOPN(
        1 ,
        FILTER( ALL( Campaigns ) ,
            Campaigns[BC #] = _Current_BC ) ,
        Campaigns[Campaign Start Date] ) ,
    Campaigns[Campaign Start Date] )

VAR _Recent_Occurence_Value =
IF( ISINSCOPE( Campaigns[BC #] ) ,
    CALCULATE(
        LASTNONBLANKVALUE( 'Campaign Start Date - Excel File (Test)'[Campaign Start Date] , MAX( Campaigns[Campaign ID] ) ) ,
        ALLEXCEPT( Campaigns , Campaigns[BC #] , Campaigns[RFP Received Date] ) ) ,
    BLANK() )

RETURN
_Recent_Occurence_Value

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

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

Note:- Since you also want to analyze the results based on second closest date, in that case, just change the starting parameter inside the “TOPN()” function i.e., from 1 to 2.

Thanks and Warm Regards,
Harsh

WIP - EDNA - Harsh.pbix (21.9 MB)

ysherriff · September 3, 2022, 10:48pm

Thanks like always Harsh. I will keep this for future reference but that is not precisely what I am looking for and apologize if I was not clear.

I am looking for the campaign just prior to the RFP Received Date.

So in the image you provided, there is BC# 114294-01 with a RFP Received Date of 1/11/2022. I am looking for a formula that would provide me the Campaign that is just prior to the RFP Date. In this instance, it would be EWCUIDA36 with a campaign start date of 12/1/2021.

Another example based on your image, is BC#114602-01 with an RFP Received Date of 1/18/2022. The campaign that would be shown would be EWCUIDA07 with a campaign start date of 12/1/2021.

So the final output should be something like this.

Thanks again Harsh

Harsh · September 5, 2022, 6:47am

Hello @ysherriff,

Since you want the “Campaign ID” based on “Campaign Start Date” which is closest as well as prior to the “RFP Received Date” then in that case, are the Campaign ID’s showcased in the final output (screenshot attached by you in the previous post) section correct? Because Campaign ID’s on those dates should have been like this -

But in your case, you’re showcasing the “Campaign Start Date” as closest as well as prior to the “RFP Received Date” but “Campaign ID” is evaluated based on the very most recent date.

For example, for BC# 112961-01 with a RFP Received Date of “25th Feb, 2022”, the Campaign Start Date closest and prior to RFP Received Date is “1st Dec, 2021” and against that date the allotted Campaign ID is “EWCUIDA50”. And you’re suggesting that the Campaign ID should be “EWCUIDA07” but this ID is allotted against the Campaign Start Date - “1st Aug, 2022”.

I’m providing below all the three measures for the reference and based on your business scenario you can implement them as required.

1). Calculation of “Campaign Start Date” closest as well as prior to the "RFP Received Date" -

Campaign Start Date - Harsh =
VAR _Max_Campaign_Start_Date =
CALCULATE(
    MAXX(
        FILTER(
            SUMMARIZE(
                Campaigns ,
                Campaigns[BC #] ,
                Campaigns[RFP Received Date] ,
                Campaigns[Campaign Start Date] ) ,
            ( DATE( YEAR( Campaigns[Campaign Start Date] ) , MONTH( Campaigns[Campaign Start Date] ) , DAY( Campaigns[Campaign Start Date] ) ) <=
              DATE( YEAR( Campaigns[RFP Received Date] ) , MONTH( Campaigns[RFP Received Date] ) , DAY( Campaigns[RFP Received Date] ) ) ) &&
              MONTH( Campaigns[Campaign Start Date] ) <> MONTH( Campaigns[RFP Received Date] ) ) ,
        Campaigns[Campaign Start Date] ) ,
    ALLEXCEPT( Campaigns , Campaigns[BC #] ) )

VAR _Results =
IF( ISINSCOPE( Campaigns[BC #] ) ,
    IF( ISBLANK( _Max_Campaign_Start_Date ) ,
        CALCULATE( MIN( Campaigns[Campaign Start Date] ) ,
            ALLEXCEPT( Campaigns , Campaigns[BC #] ) ) ,
        _Max_Campaign_Start_Date ) ,
    BLANK() )

RETURN
_Results

2). Calculation of “Campaign ID” based on very latest or recent “Campaign Start Date” -

Most Recent Campaign ID - Harsh - 1 = 
IF( ISINSCOPE( Campaigns[BC #] ) , 
    CALCULATE( 
        LASTNONBLANKVALUE( 'Campaign Start Date - Excel File (Test)'[Campaign Start Date] , MAX( Campaigns[Campaign ID] ) ) , 
        ALLEXCEPT( Campaigns , Campaigns[BC #] , Campaigns[RFP Received Date] ) ) , 
    BLANK() )

3). Calculation of “Campaign ID” based on “Campaign Start Date” closest as well as prior to the "RFP Received Date" -

Most Recent Campaign ID - Harsh - 2 =
VAR _Max_Campaign_Start_Date =
CALCULATE(
    MAXX(
        FILTER(
            SUMMARIZE(
                Campaigns ,
                Campaigns[BC #] ,
                Campaigns[RFP Received Date] ,
                Campaigns[Campaign Start Date] ) ,
            ( DATE( YEAR( Campaigns[Campaign Start Date] ) , MONTH( Campaigns[Campaign Start Date] ) , DAY( Campaigns[Campaign Start Date] ) ) <=
              DATE( YEAR( Campaigns[RFP Received Date] ) , MONTH( Campaigns[RFP Received Date] ) , DAY( Campaigns[RFP Received Date] ) ) ) &&
              MONTH( Campaigns[Campaign Start Date] ) <> MONTH( Campaigns[RFP Received Date] ) ) ,
        Campaigns[Campaign Start Date] ) ,
    ALLEXCEPT( Campaigns , Campaigns[BC #] ) )

VAR _Campaign_ID_of_Max_Campaign_Start_Date =
CALCULATE( MAX( Campaigns[Campaign ID] ) ,
    FILTER( ALLEXCEPT( Campaigns , Campaigns[BC #] ) ,
        Campaigns[Campaign Start Date] = _Max_Campaign_Start_Date ) )

VAR _If_Max_Campaign_Start_Date_Blank =
CALCULATE( MIN( Campaigns[Campaign Start Date] ) ,
    ALLEXCEPT( Campaigns , Campaigns[BC #] ) )

VAR _Campaign_ID_If_Max_Campaign_Start_Date_Blank =
CALCULATE( MAX( Campaigns[Campaign ID] ) ,
    FILTER( ALLEXCEPT( Campaigns , Campaigns[BC #] ) ,
        Campaigns[Campaign Start Date] = _If_Max_Campaign_Start_Date_Blank ) )

VAR _Results =
IF( ISINSCOPE( Campaigns[BC #] ) ,
    IF( ISBLANK( _Campaign_ID_of_Max_Campaign_Start_Date ) ,
        _Campaign_ID_If_Max_Campaign_Start_Date_Blank ,
        _Campaign_ID_of_Max_Campaign_Start_Date ) ,
    BLANK() )

RETURN
_Results

I’m also attaching the working of the 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

WIP - EDNA - Harsh v2.pbix (23.0 MB)

ysherriff · September 5, 2022, 4:39pm

Third solution is the one and I apologize for the confusion. I will keep all three for analysis and put in analyst hub and learn the syntax.

Thanks like always Harsh and have an awesome week.