IF with unrelated table conditions

[Syndicate_Admin]

Hello everyone:

I have two tables that are related through a third one (Invoices - Products - Product Code).

I want to get two pieces of information, one that tells me the amount of the product on the invoice when there is stock and another that takes away the amount of the product when there is no stock. Attached is an example of the structure of the tables.

ID Prod Code prod STK Start Date End Date

1 02937FR 0 01/01/2022 28/02/2023

1 02937FR 1 01/07/2023

2 42385PR 1 01/01/2022

3 55385MW 0 01/01/2022

For products that don't have an End Date, I've created a column called Deadline that indicates the date 12/31/2100 when the field is empty.

ID Prod Name Prod

1 Mesa

2 Chair

3 Chest of drawers

Id Prod Code Prod No. Invoice Date Invoice Amount

1 02937FR 374623 04/02/2022 150€

1 02937FR 576492 12/08/2023 155€

2 42385PR 473403 05/05/2022 56€

3 55385MW 453629 23/03/2023 86€

So that there is no mess with the dates, the approach I have made is, for example:

With Stock= IF(SUM( STK =1) & & Start Date <= Invoice Date & Invoice Date <= Deadline, Amount, 0)

Out of Stock = IF(Sum(STK = 0) & Start Date <= Invoice Date & Invoice Date <= Due Date, Amount, 0)

The thing is, it doesn't work for me. Can you help me? I'm thinking about it and I can't find the solution.

Thanks a lot!!!

[v-jiewu-msft]

Hi @Syndicate_Admin ,

Based on my testing, please try the following methods:

1.Create the simple table.

2.Create the new column to filter.

With Stock = 
var _id = 'Invoices'[ID]
vAR _invoicedate = 'Invoices'[Invoice Date]
var _sdate = CALCULATE(MAX('Product Code'[Start Date]),FILTER('Product Code','Product Code'[ID] = _id&&'Product Code'[Prod STK]=1))
var _deadline = CALCULATE(MAX('Product Code'[Deadline]),FILTER('Product Code','Product Code'[ID] = _id&&'Product Code'[Prod STK]=1))
return 
if(_sdate <= _invoicedate && _invoicedate <= _deadline, 
    CALCULATE(sum('Invoices'[Invoice Amount]),FILTER('Invoices','Invoices'[ID] = _id &&_sdate<='Invoices'[Invoice Date]&&'Invoices'[Invoice Date]<=_deadline)), 0
)

3.The result is shown below.

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Syndicate_Admin]

Good morning and thank you very much for the help.

I have created the new table and created the measure you indicate, but I have some problem with the formula because when I enter var_id it does not allow me to put the table and the ID field directly, it forces me to put a function first such as SUM or MIN and being a field without calculation I don't know which one to put...

The same thing happens to me when entering the var_invoicedate variable where it also doesn't allow me to directly enter InvoiceDate if I don't enter a function beforehand. Why does this happen?

Thanks in advance.