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.
Hello, I need to calculate production time*quantity in DAX. I have two tables and calendar table.
1) first table with production time for each item.
Item | Start date | End date | Production time |
A | 12.1.2020 | 14.2.2022 | 7 |
A | 14.2.2022 | 15.5.2023 | 7,2 |
A | 15.5.2023 | 15.5.2030 | 7,3 |
B | 30.5.2011 | 30.5.2020 | 6 |
B | 30.5.2020 | 29.6.2030 | 5,9 |
2) second table
Item | Date | Quantity |
A | 25.5.2021 | 3 |
A | 14.3.2022 | 6 |
A | 16.8.2023 | 1 |
B | 21.8.2023 | 2 |
I was thinking using {Number.From([Start date])..Number.From([End date])} in Query, but I think it is pointless. Totaly have no clue how to do it easier. Thanks for any help.
Solved! Go to Solution.
Hi @evadung ,
It seems that you want to retreieve the Production Time in second table based on Date if it falls within start date and end date of First Table.
So, You can follow below steps to achieve your requirement.
1. Create a column in Second table with below DAX expression:
FinalCalculation =
var sampledate = 'Table Second'[Date].[Date]
var productiontime =
CALCULATE (
MIN('Table'[Production time]),
FILTER (
'Table',
'Table'[Start date].[Date] <= sampledate
&& 'Table'[End date].[Date] >= sampledate && 'Table Second'[Item] = 'Table'[Item]
)
)
return productiontime*'Table Second'[Quantity]
Please refer to the below screenshot for the same.
This will give below output,
Thanks!
Inogic Professional Services Division
Power Platform and Microsoft Dynamics 365 CRM Development – All under one roof!
Drop an email at crm@inogic.com
Services: http://www.inogic.com/services/
Power Platform/Dynamics 365 CRM Tips and Tricks: http://www.inogic.com/blog/
Hi @evadung ,
It seems that you want to retreieve the Production Time in second table based on Date if it falls within start date and end date of First Table.
So, You can follow below steps to achieve your requirement.
1. Create a column in Second table with below DAX expression:
FinalCalculation =
var sampledate = 'Table Second'[Date].[Date]
var productiontime =
CALCULATE (
MIN('Table'[Production time]),
FILTER (
'Table',
'Table'[Start date].[Date] <= sampledate
&& 'Table'[End date].[Date] >= sampledate && 'Table Second'[Item] = 'Table'[Item]
)
)
return productiontime*'Table Second'[Quantity]
Please refer to the below screenshot for the same.
This will give below output,
Thanks!
Inogic Professional Services Division
Power Platform and Microsoft Dynamics 365 CRM Development – All under one roof!
Drop an email at crm@inogic.com
Services: http://www.inogic.com/services/
Power Platform/Dynamics 365 CRM Tips and Tricks: http://www.inogic.com/blog/
Thank you!
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 |
---|---|
21 | |
15 | |
14 | |
11 | |
9 |
User | Count |
---|---|
27 | |
23 | |
12 | |
11 | |
10 |