cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper IV

## Difference in Days Between 2 Measures Reaching The Same Figure

Dear Community,

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.

1 ACCEPTED SOLUTION
Super User

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

Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!

3 REPLIES 3
Super User

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

Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!

Helper IV

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 🙂

Helper IV

Thanks Pete,

It worked 🙂

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.