Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
NP2020
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:

 

DateTaskAssigneeHours
01/03/2021Task ABob8
01/03/2021Task BVic8
01/03/2021Task CLes8
02/03/2021Task ABob8
02/03/2021Task BVic8
02/03/2021Task CLes8
03/03/2021Task ABob8
03/03/2021Task BVic8
03/03/2021Task CLes8
04/03/2021Task ABob8
04/03/2021Task BVic8
04/03/2021Task CLes8

The actual table is below:

DateTaskAssigneeHours
01/03/2021Task ABob8
01/03/2021Task BVic7
01/03/2021Task CLes9
02/03/2021Task ABob5
02/03/2021Task BVic8
02/03/2021Task CLes10
04/03/2021Task CBob3
04/03/2021Task AVic7
04/03/2021Task BLes6
03/03/2021Task ABob8
03/03/2021Task BVic9
03/03/2021Task CLes10

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:

 

power_bi_column_differences_relationships.png

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
v-cazheng-msft
Community Support
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:

v-cazheng-msft_0-1617332997525.png

 

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.

 

 

View solution in original post

5 REPLIES 5
v-cazheng-msft
Community Support
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:

v-cazheng-msft_0-1617332997525.png

 

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!

Many thanks @v-cazheng-msft 

 

Apologies for not responding sooner, I have been away.

 

I will try your solution.

 

Thanks again,

Neil

amitchandak
Super User
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])

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

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?

 

power_bi_column_differences_incorrect_results.png

Thanks,

Neil

 

 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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