Difference in Days Between 2 Measures Reaching The Same Figure

[CaveOfWonders]

Dear Community,

I can't get my head round something and need your help.

I have two measures plotted on a line chart against time:

Planned Works Cumulative: (Gives me a cumulative count of rows where the Planned Works column (Date Column) is populated)
Completed Works Cumulative: (Gives me a cumulative count of rows where the Completed Works (Date Column) column is populated)

The completed works measure calculates completed works up until the end of the last month (200 at the end of June for example).

The Planned works measure is up until the end of the project.

I need to dynamicallay work out how many days ahead or behind 'Planned Works' is from the 'Completed Works'. In the current example completed works is 200 at the end of last month (June) and planned works is >= 200 on the 10th July (10 day difference), so the measure needs to display 10 days as the result.

Your help is appreciated.

[BA_Pete · Accepted solution]

Hi @CaveOfWonders ,

Try creating this measure:

_daysAheadBehind = 
DATEDIFF(
    CALCULATE(
        FIRSTNONBLANKVALUE(aTable[plannedWorks], MAX(aTable[date])),
        FILTER(
            aTable,
            aTable[plannedWorks] >= MAX(aTable[completedWorks])
        )
    ),
    LASTNONBLANKVALUE(aTable[completedWorks], MAX(aTable[date])),
    DAY
)

This gives me the following output:

I have edited my inital response to more accurately reflect your requirements.

Can the completed works ever exceed the planned works? If so, this needs more work.

Pete

[CaveOfWonders]

Hey Pete,

Thank you for the quick response.

The last non blank value for planned works is towards the end of 2021 and has a value of 1000+. The data table I provided was maybe a little misleading: Here's a slightly more accurate depiction of what's going on; with this in mind, I don't think LASTNONBLANK will work. Also these two columns are measures and not tables, however, if it worked I would have probably created calculated columns to make it work.

Sorry, I have just seen your updated response, let me have a look and get back to you; thank you 🙂

[CaveOfWonders]

Thanks Pete,

It worked 🙂