Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Sofinobi
Helper IV
Helper IV

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
tamerj1
Super User
Super User

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

View solution in original post

11 REPLIES 11
tamerj1
Super User
Super User

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

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
Super User
Super User

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.

 

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?

If you have a separate table for your products,

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

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

 

 

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
Super User
Super User

Try

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

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

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

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

thank you again my friend

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors