topic Re: look up on the date where measure 01 had same value as measure 2 in DAX Commands and Tips

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

Anonymous — Wed, 16 Apr 2025 15:37:17 GMT

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

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

lbendlin — Wed, 16 Apr 2025 20:55:30 GMT

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-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

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

Anonymous — Thu, 17 Apr 2025 06:25:17 GMT

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

this day, I am on 0% of progress,I want to know, when I supposed to achieve this progress in plan

01 Apr 23

Plan

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

Plan

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

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%

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

v-saisrao-msft — Thu, 17 Apr 2025 07:39:55 GMT

Hi Anonymous,
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.

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

lbendlin — Fri, 18 Apr 2025 13:37:08 GMT

Consider using a graphical solution

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

Anonymous — Sat, 19 Apr 2025 06:58:59 GMT

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.

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

01 Apr 23

Plan

15 Apr 23

Actual

10%

15 Apr 23

Plan

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 .

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

lbendlin — Sat, 19 Apr 2025 14:28:29 GMT

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?

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

v-saisrao-msft — Mon, 21 Apr 2025 04:34:03 GMT

Hi Anonymous,
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.

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

v-saisrao-msft — Thu, 24 Apr 2025 06:48:11 GMT

Hi Anonymous,

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.

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

v-saisrao-msft — Mon, 28 Apr 2025 04:17:26 GMT

Hi Anonymous,

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.

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

Anonymous — Sat, 03 May 2025 21:40:07 GMT

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