topic Re: Optimize DAX "Last 3 month sales" in DAX Commands and Tips

Optimize DAX "Last 3 month sales"

Sofinobi — Wed, 22 Mar 2023 13:50:50 GMT

helllo All,

i have a measure to calculate the sales of last 3 months for each product (excluding the current month, it calculates december, january and febrary)

but the measure is too slow, it takes about 8 to 10 seconde to refresh, the fact table i about 4 millions row
please if you can help me to optimize it.

Vente Qte 3M =

CALCULATE (

SUMX (

COM_DocumentDetail,

IF (

DATEDIFF ( COM_DocumentDetail[CreationDate], TODAY (), MONTH ) <= 3

&& DATEDIFF ( COM_DocumentDetail[CreationDate], TODAY (), MONTH ) >= 1,

[Vente Net Qte],

)

)
thank you so much

Re: Optimize DAX "Last 3 month sales"

johnt75 — Wed, 22 Mar 2023 13:55:40 GMT

Try

Vente Qte 3M = CALCULATE ( SUM ( COM_DocumentDetail[Vente Net Qte] ), DATESBETWEEN ( COM_DocumentDetail[CreationDate], EOMONTH ( TODAY (), -4 ) + 1, EOMONTH ( TODAY (), -1 ) ) )

Re: Optimize DAX "Last 3 month sales"

MohammadLoran25 — Wed, 22 Mar 2023 14:03:19 GMT

Hi Sofinobi

Is it necessary to use SUMX?

Please provide more information.

You can do it easire using your date dimension.

And Also the reason that it is slow is both for SUMX and IF Functions.

Re: Optimize DAX "Last 3 month sales"

Sofinobi — Wed, 22 Mar 2023 14:11:50 GMT

thank you for your answer,

there is an error in this code,

SUM ( COM_DocumentDetail[Vente Net Qte] )

the [vente net Qte] is not a column from the table, it's an other measure that calculates the total sales quantity

Re: Optimize DAX "Last 3 month sales"

Sofinobi — Wed, 22 Mar 2023 14:23:49 GMT

the SUMX is for to get the sum for each product separately
that i can represent in graphics or use it in other calculations,
how can i do that with my date table?

Re: Optimize DAX "Last 3 month sales"

tamerj1 — Wed, 22 Mar 2023 14:34:14 GMT

Hi Sofinobi

please try

Vente Qte 3M =
SUMX (
VALUES ( COM_DocumentDetail[CreationDate] ),
VAR Diff =
DATEDIFF ( COM_DocumentDetail[CreationDate], TODAY (), MONTH )
RETURN
IF ( Deff <= 3 && Deff >= 1, [Vente Net Qte], 0 )
)

Re: Optimize DAX "Last 3 month sales"

johnt75 — Wed, 22 Mar 2023 14:39:43 GMT

You can change it to

Vente Qte 3M = CALCULATE ( SUMX ( COM_DocumentDetail, [Vente Net Qte] ), DATESBETWEEN ( COM_DocumentDetail[CreationDate], EOMONTH ( TODAY (), -4 ) + 1, EOMONTH ( TODAY (), -1 ) ) )

Re: Optimize DAX "Last 3 month sales"

MohammadLoran25 — Wed, 22 Mar 2023 14:43:21 GMT

Okay.

It seems that SUMX is a must in your case because it should result in your desired result. If I'm wrong please let me know. It is what I understood from your explanation.

CALCULATE (
    SUMX ( VALUES ( 'COM_DocumentDetail'[ProductId] ), [Vente Net Qte] ),
    DATESINPERIOD (
        DateTable[Date],
        STARTOFMONTH ( DATEADD ( DateTable[Date], -3, MONTH ) ),
        +2,
        MONTH
    )
)

If this answer solves your problem, please mark it as an accepted solution so the others would find what they need easier.
Regards,
Loran

Re: Optimize DAX "Last 3 month sales"

Sofinobi — Wed, 22 Mar 2023 14:47:32 GMT

ohh great work my friend, thank you so much for your help, it works great
thank you again, now i'll try it for my other measures

like this one;

Moyen Vente Qte / 3Mois =

VAR _months = 3

VAR _SumLast3Months =

CALCULATE (

SUMX (

COM_DocumentDetail,

IF (

DATEDIFF ( COM_DocumentDetail[CreationDate], TODAY (), MONTH ) <= _months

&& DATEDIFF ( COM_DocumentDetail[CreationDate], TODAY (), MONTH ) >= 1,

[Vente Net Qte],

)

RETURN

_SumLast3Months / _months

Re: Optimize DAX "Last 3 month sales"

MohammadLoran25 — Wed, 22 Mar 2023 14:47:56 GMT

If you have a separate table for your products,

you can replace VALUES ( 'COM_DocumentDetail'[ProductId] with 'ProductTable'

Re: Optimize DAX "Last 3 month sales"

Sofinobi — Wed, 22 Mar 2023 14:56:14 GMT

thank you so much for your answer, but it seems that it returns values are not the same as my old measure,
i have the solution from tamerj1 , and it works perfectely
thanks again

Re: Optimize DAX "Last 3 month sales"

Sofinobi — Wed, 22 Mar 2023 15:13:44 GMT

thank you so much for your answer, the code works great, but the code from tamerj1 is faster.

thank you again my friend