Forum Discussion

Anonymous_12345's avatar
Anonymous_12345
New Member
6 months ago
Solved

DAX total vs row context problem

M_NPD_SALES_L12M_v1($) = SUMX( VALUES(DIM_INVOICE_DATE[INVOICE_DATE]), -- or Month, or Date key VAR Invoice_Date = MAX(DIM_INVOICE_DATE[INVOICE_DATE]) RETURN CALCULATE( SUM(FACT_SALES[SALE_EXTENDED_N...
Tips and Tricks
  • ryan_mayu's avatar
    ryan_mayu
    6 months ago

    Anonymous_12345 

    pls try this

     

    M_NPD_SALES_L12M_v2($) =
    SUMX(
    VALUES(DIM_INVOICE_DATE[INVOICE_DATE]),
    VAR Invoice_Date = DIM_INVOICE_DATE[INVOICE_DATE]
    RETURN
    CALCULATE(
    SUM(FACT_SALES[SALE_EXTENDED_NET_AMOUNT]),
    FACT_SALES[INVOICE_DATE] = Invoice_Date, 
    FILTER(
    DIM_PRODUCT,
    NOT ISBLANK(DIM_PRODUCT[LAUNCH_DATE]) &&
    DATEDIFF(DIM_PRODUCT[LAUNCH_DATE], Invoice_Date, MONTH) <= 12 &&
    DATEDIFF(DIM_PRODUCT[LAUNCH_DATE], Invoice_Date, MONTH) >= 0
    )
    )
    )

     

    if this does now work, pls provide some sample data and expected output

DanieleUgoCopp's avatar
DanieleUgoCopp
Super User
6 months ago

Hello,
as far as I understand SUMX iterates over dates, but inside the iteration the MAX of the invoice date does not behave the same way for totals as it does for individual rows, so the 12-month logic ends up being evaluated differently.
One possible approach could be to move the date logic into the CALCULATE filter context more explicitly, for example by using a single selected date or by reshaping the filter so it does not depend on MAX inside the iterator.