Calculated Column Help

[Anonymous]

Hey all, I'm working on a project management dashboard. I can't show due to sensitive data. I can try to create a mockup if needed. The main tables I have are:

• Date Table
• Worker Table
• Project Table
• Project Forecast Table (forecasted hours per project, worker, etc)
• Project Actuals Table (actual hours charged per project, worker, etc)

All of the tables talk to each other in a 1->many relationship except Forecast and Actuals. Thanks to this wonderful community, this was resolved by showing me how to better manage table relationships.

Now I'm trying to create 3 new calculated columns:

• Forecasted_Cost - Worker rate * worker forecasted hours (This one I have working and math checks out). DAX below:
  • Forecasted_Cost = 'Forecast Hours by Worker & Project'[Forecasted Hours] * 'Forecast Hours by Worker & Project'[Rate]
• Actuals_Cost_Extract - I'm pulling values from the 'Actuals Cost' column in the Project Actuals table. DAX below that seems correct and is finally only showing $$ on only dates where a worker actually worked:

Actuals_Cost_Extract = CALCULATE(FIRSTNONBLANK('IAT All Worker Timesheet Actual'[Actuals Cost], 1),

FILTER(ALLNOBLANKROW('IAT All Worker Timesheet Actual'),'IAT All Worker Timesheet Actual'[Date] = 'Forecast Hours by Worker & Project'[Date]))

• Remaining_Forecast_Cost = Should be 'Forecasted_Cost - Actuals_Cost'. Below is the closes I've gotten with DAX:
  • Remain_Forecast_Cost = CALCULATE(SUM('Forecast Hours by Worker & Project'[Forecasted_Cost]) - SUM('Forecast Hours by Worker & Project'[Actuals_Cost_Extract]),

    FILTER(ALLNOBLANKROW('IAT All Worker Timesheet Actual'), 'Forecast Hours by Worker & Project'[Date]))

Here is a quick mockup of what I am trying to produce. Currently, I cannot get Remaining Forecast Cost to show up anywhere close to accurate.

Worker	Project	Forecasted Hours	Date	Rate	Forecasted_ Cost	Actuals_Cost_ Extract	Remaining_Forecast_ Cost
John Smith	Project 1	2	Jan 05, 2022	$100	$200	$200
John Smith	Project 1	2	Jan 06, 2022	$100	$200	$100	$100
John Smith	Project 1	2	Mar 10, 2022	$100	$200		$200
Bob Bob	Project 1	3	Jan 05, 2022	$100	$300	$300
Bob Bob	Project 1	3	Jan 06, 2022	$100	$300	$100	$200
Bob Bob	Project 1	3	Mar 10, 2022	$100	$300		$300

[v-zhangti · Accepted solution]

Hi, @Anonymous 

Please check the following methods.

Column:

Forecasted_Cost = [Forecasted_Hours]*[Rate]

Actuals_Cost_Extract = 
CALCULATE(FIRSTNONBLANK('IAT All Worker Timesheet Actual'[Actuals Cost], 1),
FILTER(ALLNOBLANKROW('IAT All Worker Timesheet Actual'),[Date] = 'Forecast Hours by Worker & Project'[Date]
&&[Worker]=EARLIER('Forecast Hours by Worker & Project'[Worker])))

Remaining_Forecast_Cost = [Forecasted_Cost]-[Actuals_Cost_Extract]

Not sure I understand correctly, is this the output you are expecting? If the method I provided above can't solve your problem, what's your expected result? Could you please provide more details for it?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you so much!! That is exactly what I was looking for and just couldn't quite get it. I plugged your DAX into my original pbix file and it worked!