Calculate production time*quantity

[evadung]

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.

[SamInogic · Accepted 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/

[evadung]

Thank you!