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_NET_AMOUNT]), FILTER( DIM_PRODUCT, NOT ISBLANK(DIM_PRODUCT[LAUNCH_DATE]) && DATEDIFF( DIM_PRODUCT[LAUNCH_DATE], Invoice_Date, MONTH ) <= 12 ) ) )   Resulting output is not correct. How to resolve this ?

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

5 Replies

  • 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.

  • 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

  • v-prasare's avatar
    v-prasare
    Community Support
    6 months ago

    Hi Anonymous_12345,

    We would like to confirm if our community members answer resolves your query or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.

     

     

    Thank you for your patience and look forward to hearing from you.
    Best Regards,
    Prashanth Are
    MS Fabric community support

  • v-prasare's avatar
    v-prasare
    Community Support
    6 months ago

    Hi @Anonymous_12345,

    We would like to confirm if our community members answer resolves your query or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.

     

     

    Thank you for your patience and look forward to hearing from you.
    Best Regards,
    Prashanth Are
    MS Fabric community support