Forum Discussion
Calculate production time*quantity
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.
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 [email protected]
Services: http://www.inogic.com/services/
Power Platform/Dynamics 365 CRM Tips and Tricks: http://www.inogic.com/blog/
2 Replies
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 [email protected]
Services: http://www.inogic.com/services/
Power Platform/Dynamics 365 CRM Tips and Tricks: http://www.inogic.com/blog/
Thank you!
