Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon'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.
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:
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:
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]))
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 |
Solved! Go to Solution.
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]
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.
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]
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!
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
143 | |
78 | |
63 | |
52 | |
47 |
User | Count |
---|---|
212 | |
82 | |
61 | |
60 | |
58 |