SUMX with two tables

[Anonymous]

Hello,

Need some help with DAX.

I have two tables Historical Inventory data and Calendar table.

I need to calculate measure LS quantity. For the calculation I use data:

• Active days - sums up Active column
  LS days - sums up  LS column
• Trusted - formula is if LS days is less than Active days*0.8 returns zero, so it can't be used in calculations

LS quantity = 
VAR tbl= SUMMARIZE('Historical Inventory';'Historical Inventory'[KEY1]; "Active";[Active days];"LS"; [LS days]; "Sales"; SUM('Historical Inventory'[Sales]); "Trust"; [Trusted])
RETURN
    SUMX(FILTER(tbl; [Trust]=1); 
        ROUND(
             DIVIDE([Sales]; MAX([Active]-[LS days];1))*[LS days];1))

Everything works fine if I want statistical data for Item No., but when I want to see by the date it returns zero.

How should I modify DAX, to return correct LS quantity for both: Item No and date?

The issue to consider: this formula must be efficient, because it will be used in the big data table, so the latency must be as small as it can be.

Example file:

https://drive.google.com/file/d/14f_g9ht_hQFnYbVlemucBa1yfB_PnwS0/view?usp=sharing

[TeigeGao]

Hi @Anonymous ,

We can use the following DAX query to get trusted:

Trusted =
VAR Activedays =
    CALCULATE (
        COUNTROWS ( 'Historical Inventory' ),
        'Historical Inventory'[Active] = 1
    )
VAR LSdays =
    CALCULATE (
        COUNTROWS ( 'Historical Inventory' ),
        'Historical Inventory'[LS] = 1
    )
RETURN
    IF ( LSdays < Activedays * 0.8, 0, 1 )

Best Regards,

Teige

[Anonymous]

Hi @TeigeGao,

Thanks, for support, but maybe there was some misunderstanding, I don't have issues with calculating Trusted.

My problem is that I need to calculate LS quantity for items and for a date with one formula because for now, it calculates just for the item.