Formula for cumulate sales 12 month back

[bogdan_re]

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

[Eric_Zhang]

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

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