DAX total vs row context problem

[Anonymous_12345]

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 ?

[v-prasare]

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]

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

[krishnakanth240]

Hi @Anonymous_12345 

Please share sample data and the output you want to view.

[ryan_mayu · Accepted solution]

@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]

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.