cancel
Showing results for
Did you mean:
Helper II

## Optimize DAX "Last 3 month sales"

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

1 ACCEPTED SOLUTION
Super User

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

11 REPLIES 11
Super User

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

Helper II

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
Solution Sage

Is it necessary to use SUMX?

You can do it easire using your date dimension.

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

Helper II

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?

Solution Sage

If you have a separate table for your products,

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

Solution Sage

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], -3MONTH ) ),
+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

Helper II

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

Super User

Try

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

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

Super User

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 )
)
)
``````
Helper II

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

thank you again my friend

Announcements

#### Power BI Community Changes

Check out the changes to the Power BI Community announced at Build.

#### Power BI May 2023 Update

Find out more about the May 2023 update.