Forum Discussion

bogdan_re's avatar
bogdan_re
New Member
9 years ago

Formula for cumulate sales 12 month back

Hi experts,   I'm using the power BI tool since last year and i build very nice and intresting reports. Now, I want to build a graph which will show me the trends of the sales for the current year ...
Need Help
Eric_Zhang's avatar
Eric_Zhang
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

bogdan_re

Check a demo in the attached PBIX. See the main measure below. OrderIndicator is a calculated column shows the data order based on the date.

 

 

SumOfLast12Months =
IF (
    MAX ( 'Table'[OrderIndicator] ) < 12,
    BLANK (),
    SUMX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[OrderIndicator]
                >= MAX ( 'Table'[OrderIndicator] ) - 11
                && 'Table'[OrderIndicator] <= MAX ( 'Table'[OrderIndicator] )
        ),
        'Table'[SALES]
    )
)

 

 

By the way, when posting sample data, please post it as plain text or attach a csv file. The snapshot doesn't help to much to generate the test data.

 

 

  • bogdan_re's avatar
    bogdan_re
    New Member
    9 years ago

    Hi,

     

    Thank you very much for the formula. It is working, but with filter all from table does not gave me the possibility to filter the result by other criteria.

    The sales table is comming from a query which return more columns and I whant to compare the result by ItmsGrpNam which is the product group

     

    --DROP TABLE #Tmp

    SET DATEFORMAT DMY

    SELECT
        T2.[SWW],
        T0.CardCode AS ClientCode,
        T0.CardName AS ClientName,
        T3.e_mail,
    T3.u_industry,
    T3.country,
    T3.County,
    T3.MailCounty,
    T3.MailCity,
        T4.GroupName,
        T5.SlpName AS SalesEmployee,
        T6.CountyB AS County,
        T6.CITYB AS City,
        'IN' AS DocType,
        T0.DocNum AS 'DocNumber',
        T0.DocDate,
        T1.ItemCode,
        T1.Dscription,
    t1.TreeType,
        T1. U_SerialNo AS SerialNo,
        T7.ItmsGrpNam,
        T1.Quantity,
        T1.[LineTotal]*((100-isnull(T0.DISCPRCNT,0))/100) As LineTotal,
        T1.[GrssProfit] As GrssProfit,
    t2.avgprice,
    t2.u_categ_fmr
    INTO #TMP
    FROM
        OINV T0  
        INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry
        LEFT JOIN OITM T2 ON T1.ItemCode = T2.ItemCode
        INNER JOIN OCRD T3 ON T0.CardCode = T3.CardCode
        INNER JOIN OCRG T4 ON T3.GroupCode = T4.GroupCode
        LEFT JOIN OSLP T5 ON T0.SlpCode = T5.SlpCode
        LEFT JOIN INV12 T6 ON T0.DocEntry = T6.DocEntry
        LEFT JOIN OITB T7 ON T2.ItmsGrpCod = T7.ItmsGrpCod
           
        
    WHERE
        ---T0.DocDate BETWEEN [%0] AND [%1]
        ((T0.DocType = 'I' AND T2.ItmsGrpCod <> '157') OR (T0.DocType = 'S' AND T1.AcctCode NOT LIKE  '472%'))
        
        
    UNION ALL


    SELECT
        Z2.[SWW],
        Z0.CardCode AS ClientCode,
        Z0.CardName AS ClientName,
    Z3.E_mail,
    Z3.u_industry,
    Z3.country,
    Z3.County,
    Z3.MailCounty,
    Z3.MailCity,
        Z4.GroupName,
        Z5.SlpName AS SalesEmployee,
        Z6.CountyB AS County,
        Z6.CITYB AS City,
        'CN',
        Z0.DocNum,
        Z0.DocDate,
        Z1.ItemCode,
        Z1.Dscription,
    z1.TreeType,
    Z1. U_SerialNo AS SerialNo,
        Z7.ItmsGrpNam,
        -Z1.Quantity,
        -Z1.[LineTotal]*((100-isnull(Z0.DISCPRCNT,0))/100),
        -Z1.[GrssProfit] ,
    z2.avgprice,
    Z2.u_categ_fmr
    FROM
        ORIN Z0  
        INNER JOIN RIN1 Z1 ON Z0.DocEntry = Z1.DocEntry
        LEFT JOIN OITM Z2 ON  Z1.ItemCode = Z2.ItemCode
        INNER JOIN OCRD Z3 ON Z0.CardCode = Z3.CardCode
        INNER JOIN OCRG Z4 ON Z3.GroupCode = Z4.GroupCode
        LEFT JOIN OSLP Z5 ON Z0.SlpCode = Z5.SlpCode
        LEFT JOIN RIN12 Z6 ON Z0.DocEntry = Z6.DocEntry
        LEFT JOIN OITB Z7 ON Z2.ItmsGrpCod = Z7.ItmsGrpCod
           WHERE
        ---Z0.DocDate BETWEEN [%0] AND [%1]
        ((Z0.DocType = 'I' AND Z2.ItmsGrpCod <> '157') OR (Z0.DocType = 'S' AND Z1.AcctCode NOT LIKE '472%'))

    SELECT * FROM #tmp