Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

SUMX with two tables

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

 

 

2 Replies

  • TeigeGao's avatar
    TeigeGao
    Icon for Solution Sage rankSolution Sage

    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's avatar
      Anonymous
      Not applicable

      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.