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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
DJLight890
Helper II
Helper II

Calculated Column Help

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.

WorkerProject

Forecasted

Hours

DateRate

Forecasted_

Cost

Actuals_Cost_

Extract

Remaining_Forecast_

Cost

John Smith

Project 12Jan 05, 2022$100$200$200 
John SmithProject 12Jan 06, 2022$100$200$100$100
John SmithProject 12Mar 10, 2022$100$200 $200
Bob BobProject 13Jan 05, 2022$100$300$300 
Bob BobProject 13Jan 06, 2022$100$300$100$200
Bob BobProject 13Mar 10, 2022$100$300 $300
 
1 ACCEPTED SOLUTION
v-zhangti
Community Support
Community Support

Hi, @DJLight890 

 

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]

vzhangti_0-1645413455328.png

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.

View solution in original post

2 REPLIES 2
v-zhangti
Community Support
Community Support

Hi, @DJLight890 

 

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]

vzhangti_0-1645413455328.png

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.

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!

Helpful resources

Announcements
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! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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