Get Total sales amont of last 5 years

[vipin_jha123]

Hi ,

I have sales table , in in that table I have data of last 5 years.

Now My requirment is to get the total sales amount of last 5 years like  2016,2015,2014,2013,2012

similarly Can I also get the total sales amonut of last 5 years same month

for example if current month is aug 2017 , so I can se thae 1ug 2016,aug 2015 and so on

Thanks in advance

Vipin Jha

[v-qiuyu-msft · Accepted solution]

Hi @vipin_jha123,

 You can create a calendar table, then create a measure like below:

Measure = CALCULATE(SUM(Table1[Value]),FILTER('Table1',MONTH('Table1'[Date])=MONTH(MAX('Calendar'[Date])) && YEAR('Table1'[Date])>=YEAR(MAX('Calendar'[Date]))-5 && YEAR('Table1'[Date])<=YEAR(MAX('Calendar'[Date]))))

I create a report use dummy data(1/1/2016 - 12/31/2017) to calculate the total value for the same month for last year and current year for your reference, for detail information, you can see attached pbix file.

Best Regards,
QiuyunYu

[marcorusso]

This could be complex to achieve using the standar Time Intelligence functions in DAX, unless you use UNION and you append multiple results provided by DATEADD.

CALCULATE (
     [measure],
     UNION (
         UNION (
            VALUES ( 'Date'[Date] ),
            DATEADD ( 'Date'[Date], -1, YEAR )
        ),
        UNION ( 
            UNION ( 
                DATEADD ( 'Date'[Date], -2, YEAR ),
                DATEADD ( 'Date'[Date], -3, YEAR )
            ),
            DATEADD ( 'Date'[Date], -4, YEAR )
       )
    )
)
       

Another approach is using a custom time inlelligence such as the Time Patterns: http://www.daxpatterns.com/time-patterns/

Marco Russo - SQLBI