Skip to main content
cancel
Showing results for
Search instead 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

Frequent Visitor

## Calculate difference between columns in two tables related by dates

Hi -

I am looking for some help trying to calculate the value of the difference between two columns that are related via a number of tables.

I have a table containing a schedule of work and another table showing what actually happened. The planned table is below:

 Date Task Assignee Hours 01/03/2021 Task A Bob 8 01/03/2021 Task B Vic 8 01/03/2021 Task C Les 8 02/03/2021 Task A Bob 8 02/03/2021 Task B Vic 8 02/03/2021 Task C Les 8 03/03/2021 Task A Bob 8 03/03/2021 Task B Vic 8 03/03/2021 Task C Les 8 04/03/2021 Task A Bob 8 04/03/2021 Task B Vic 8 04/03/2021 Task C Les 8

The actual table is below:

 Date Task Assignee Hours 01/03/2021 Task A Bob 8 01/03/2021 Task B Vic 7 01/03/2021 Task C Les 9 02/03/2021 Task A Bob 5 02/03/2021 Task B Vic 8 02/03/2021 Task C Les 10 04/03/2021 Task C Bob 3 04/03/2021 Task A Vic 7 04/03/2021 Task B Les 6 03/03/2021 Task A Bob 8 03/03/2021 Task B Vic 9 03/03/2021 Task C Les 10

Both tables have a number of one to many related fields for date, task, assignee as well as hours forecast/booked. The relationship table is shown below:

When I try to make a calculated column on the forecast table, looking to perform Forecast Hours - Actual Hours, I cannot reference the Actual Hours column or use the RELATED function.

Please can you recommend how to approach this?

Thanks

Neil

1 ACCEPTED SOLUTION
Community Support

Hi @NP2020

You can create a Measure.

``````Variance =

IF (

HASONEFILTER ( Staff[Names] ),

SELECTEDVALUE ( Forecast[Hours] ) - SELECTEDVALUE ( Actual[Hours] ),

SUM ( Forecast[Hours] ) - SUM ( Actual[Hours] )

)``````

The result looks like this:

For more details, you can refer the attached pbix file.

Best Regards

CaiyunZheng

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

5 REPLIES 5
Community Support

Hi @NP2020

You can create a Measure.

``````Variance =

IF (

HASONEFILTER ( Staff[Names] ),

SELECTEDVALUE ( Forecast[Hours] ) - SELECTEDVALUE ( Actual[Hours] ),

SUM ( Forecast[Hours] ) - SUM ( Actual[Hours] )

)``````

The result looks like this:

For more details, you can refer the attached pbix file.

Best Regards

CaiyunZheng

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

you just don't know how you helped me a lot with this formula! Thank you so much!

Frequent Visitor

Many thanks @v-cazheng-msft

Apologies for not responding sooner, I have been away.

I will try your solution.

Thanks again,

Neil

Super User

@NP2020 , You can create a new column in forecast like

sumx(filter(Actual, actual[Assignee] =forecast[Assignee] && actual[Date] =forecast[Date] && actual[Task] =forecast[Task] ),[Hours])

Ideally with common dimensions you should be able to create a measure and use

Diff =

Sum(Forecast[Hour]) - sum(Actual[Hour])

Frequent Visitor

Many thanks @amitchandak,

I have been able to implement this, although I notice that if someone books hours to a task that they were not forecast to do (highlighted below), the variance does not calculate. Is there any way to fix this?

Thanks,

Neil

## Helpful resources

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.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors