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 in a specific way; actually what I'm trying to do is to compare by months the cumulate sales from today, back 12 months for the last 2 years.

So the graph will show me on the x axis the months and on the Y axis the Sales **bleep** last 12 Months

What i'm asking you is  to calculate the sum of sales of last 12 months for each month

 

Thanks in advance

 

Need Help

2 Replies

  • 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