look up on the date where measure 01 had same value as measure 2

[Abdullah_Dax]

hello

i am trying to look up on the first date where the measure %Plan had the nearest value to the value measure %Actual, then mark it in the related row of %actual .

example

in 27 may, the actual is 0.4, where i was supposed to achieve 0.4 (or nearrest value 0.41) by 10 jun,

i need to retrieve 10 juin as value in the column A  row of 27 may,

thanks 

[v-saisrao-msft · Accepted solution]

Hi @Abdullah_Dax,
Thank you for reaching out to the Microsoft Fabric Forum Community.

Thank you @lbendlin, for your insights.

Thanks for sharing the detailed scenario and sample data. 
To achieve your goal of identifying the date in Plan where the value is closest to each Actual, I’ve created a solution using a separate Plan table and a DAX measure that compares values and returns the nearest match date. 

I’ve attached a sample .pbix file for your reference.

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you. 

[Abdullah_Dax · Accepted solution]

hi

@v-saisrao-msft 

thanks for your help

indeed, it doesnt work as per your way, but i found another DAX measure to return what i wnated,

through this formula, i can find the nearest date where i supposed to be with actual progress, if it's behind my data date it means i am late, and if it's ahead of my data date  it means i am early in my project.

by the result date, i can determine the exact amount of days either late or early

find the example: 

anyway, this measure need some improvement...

Delay =                

VAR Curr_Act_Prog = [% Cur Actual]    // return actual progress
VAR Pln_tbl =                         // creat a table of only planned progress
    FILTER (
        ALL ( Schedules_Progress ),
        Schedules_Progress[Type] = "Plan"
            && Schedules_Progress[Item] = "Progress"
    )
VAR Diff_Tbl =             // create a table of smallest value between current planned progress and the actual progress
    TOPN (
        1,
        ADDCOLUMNS (
            Pln_tbl,
            "Delta", Schedules_Progress[Value] - Curr_Act_Prog,
            "ABS_Delta", ABS ( Schedules_Progress[Value] - Curr_Act_Prog )
        ),
        [ABS_Delta], ASC
    )
VAR Min_Diff =
    MINX ( Diff_Tbl, [Delta] )  // return the smallest value [positive or negative]
VAR Nearest_Plan_Val = Curr_Act_Prog + Min_Diff   // add the value to the actual progress to find the related  planned value
VAR Srch_Tbl =             // creat a table to find the diffirence between dates for same condition
    ADDCOLUMNS (
        Pln_tbl,

        "Diff_PL_Val", [% Plan] = Nearest_Plan_Val,
        
        "ABS_Rslt_Dt_Diff", ABS ( Schedules_Progress[Date] - MAX ( Schedules_Progress[Date] ) )
    )
VAR Nearest_Plan_Date =           // return row of the smallest value 
    TOPN ( 1, Srch_Tbl, [Diff_PL_Val], DESC, [ABS_Rslt_Dt_Diff], ASC )
VAR result =
    MAXX ( Nearest_Plan_Date, Schedules_Progress[Date] ) // find the nearest date 
RETURN
    result

[v-saisrao-msft]

Hi @Abdullah_Dax,

We haven’t heard back from you regarding your issue. If it has been resolved, please mark the helpful response as the solution and give a ‘Kudos’ to assist others. If you still need support, let us know.

Thank you.

[v-saisrao-msft]

Hi @Abdullah_Dax,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-saisrao-msft · Accepted solution]

Hi @Abdullah_Dax,
Thank you for reaching out to the Microsoft Fabric Forum Community.

Thank you @lbendlin, for your insights.

Thanks for sharing the detailed scenario and sample data. 
To achieve your goal of identifying the date in Plan where the value is closest to each Actual, I’ve created a solution using a separate Plan table and a DAX measure that compares values and returns the nearest match date. 

I’ve attached a sample .pbix file for your reference.

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you. 

[Abdullah_Dax]

Thanks @lbendlin 

find here below, the raw data with wanted result column,

in each date with actual progress, i want to know when i was supposed or will be supposed to achieve this progress in plan values (approximate date)

Raw Data
Date	Type	Max of Value		wanted result, throught measure
01 Apr 23	Actual	0%	this day, I am on 0% of progress,I want to know, when I supposed to achieve this progress in plan	01 Apr 23
01 Apr 23	Plan	0%
15 Apr 23	Actual	10%	this day, I am on 10% of progress,I want to know, when I supposed to achieve this progress in plan	15 Apr 23
15 Apr 23	Plan	1%
29 Apr 23	Actual	25%	this day, I am on 25% of progress,I want to know, when I supposed to achieve this progress in plan	13 May 23
29 Apr 23	Plan	11%
13 May 23	Actual	45%	this day, I am on 45% of progress,I want to know, when I supposed to achieve this progress in plan	10 Jun 23
13 May 23	Plan	21%
27 May 23	Actual	50%	this day, I am on 50% of progress,I want to know, when I supposed to achieve this progress in plan	10 Jun 23
27 May 23	Plan	31%
10 Jun 23	Actual	59%	this day, I am on 59% of progress,I want to know, when I supposed to achieve this progress in plan	24 Jun 23
10 Jun 23	Plan	41%
24 Jun 23	Actual	66%	this day, I am on 66% of progress,I want to know, when I supposed to achieve this progress in plan	08 Jul 23
24 Jun 23	Plan	51%
08 Jul 23	Actual	88%	this day, I am on 88% of progress,I want to know, when I supposed to achieve this progress in plan	05 Aug 23
08 Jul 23	Plan	61%
22 Jul 23	Actual	90%	this day, I am on 90% of progress,I want to know, when I supposed to achieve this progress in plan	05 Aug 23
22 Jul 23	Plan	71%
05 Aug 23	Actual	90%	this day, I am on 90% of progress,I want to know, when I supposed to achieve this progress in plan	05 Aug 23
05 Aug 23	Plan	81%
19 Aug 23	Actual	90%	this day, I am on 90% of progress,I want to know, when I supposed to achieve this progress in plan	05 Aug 23
19 Aug 23	Plan	91%
02 Sep 23	Actual	90%	this day, I am on 90% of progress,I want to know, when I supposed to achieve this progress in plan	05 Aug 23
02 Sep 23	ForeCast	90%
02 Sep 23	Plan	100%
16 Sep 23	ForeCast	91%
16 Sep 23	Plan	100%
30 Sep 23	ForeCast	100%
30 Sep 23	Plan	100%
14 Oct 23	ForeCast	100%
14 Oct 23	Plan	100%
28 Oct 23	ForeCast	100%
28 Oct 23	Plan	100%

[lbendlin]

Consider using a graphical solution

[Abdullah_Dax]

hello @lbendlin 

yes, indeed, but i need it to be calculated for each actual date, so that i will be able to calculat the delay days (or ahead of schedule days) between actual value's date and planned value's date

well @lbendlin ,

in your first answer, you said that we can't measure a measure.

ok

what if my data is materialized in a table below.

is there any measure to calculate the date where the planned progress is equal or near the Actual Progress value ?

Date	Type	Value of progress
01 Apr 23	Actual	0%
01 Apr 23	Plan	0%
15 Apr 23	Actual	10%
15 Apr 23	Plan	1%
29 Apr 23	Actual	25%
29 Apr 23	Plan	11%
13 May 23	Actual	45%
13 May 23	Plan	21%
27 May 23	Actual	50%
27 May 23	Plan	31%
10 Jun 23	Actual	59%
10 Jun 23	Plan	41%
24 Jun 23	Actual	66%
24 Jun 23	Plan	51%
08 Jul 23	Actual	88%
08 Jul 23	Plan	61%
22 Jul 23	Actual	90%
22 Jul 23	Plan	71%
05 Aug 23	Actual	90%
05 Aug 23	Plan	81%
19 Aug 23	Actual	90%
19 Aug 23	Plan	91%
02 Sep 23	Actual	90%
02 Sep 23	ForeCast	90%
02 Sep 23	Plan	100%
16 Sep 23	ForeCast	91%
16 Sep 23	Plan	100%
30 Sep 23	ForeCast	100%
30 Sep 23	Plan	100%
14 Oct 23	ForeCast	100%
14 Oct 23	Plan	100%
28 Oct 23	ForeCast	100%
28 Oct 23	Plan	100%

the outcomes should be as per @v-saisrao-msft mentioned in his solution [but he separated the table

thanks in advance .

[v-saisrao-msft]

Hi @Abdullah_Dax,
I wanted to check if you had the opportunity to review the information provided by me & @lbendlin. Please feel free to contact us if you have any further questions. If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[lbendlin]

but i need it to be calculated for each actual date, so that i will be able to calculat the delay days (or ahead of schedule days) between actual value's date and planned value's date

You don't need to calculate it for each actual date, but for each actual value.  Meaning that you need to find out when the actual value is matched by the plan. That is non-deterministic as there may be multiple matches and the match in the plan may not fall on a "whole"  date.   

So while that is technically possible to do it comes with a lot of uncertainties.

Here is a first step - finding the date range of the Plan dates that may cover an actual date

Excluding the Forecast makes it a little less messy

Next step would be to use LINESTX to find the line segment characteristics and to identify the intersection with the date axis where the plan value matches the actual value.

What are you ultimately trying to show with the report?  Once you have the deltas, what are you going to do with them?

[lbendlin]

You cannot measure a measure directly. Either materialize it first, or create a separate measure that implements the entire business logic.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...