Optimize DAX "Last 3 month sales"

[Sofinobi]

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

                0

            )

        )

    )
thank you so much

[tamerj1 · Accepted solution]

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 )
)

[Sofinobi]

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

                0

            )

        )

    )

RETURN

    _SumLast3Months / _months

[MohammadLoran25]

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.

[Sofinobi]

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?

[MohammadLoran25]

If you have a separate table for your products,

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

[MohammadLoran25]

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

[Sofinobi]

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

[johnt75]

Try

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

[Sofinobi]

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

[johnt75]

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 )
    )
)

[Sofinobi]

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

thank you again my friend