The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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!
User | Count |
---|---|
27 | |
12 | |
8 | |
8 | |
5 |
User | Count |
---|---|
31 | |
15 | |
12 | |
11 | |
7 |