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

ryan_mayu's avatar
ryan_mayu
Super User
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