Compare against last year selected month

[AlejandroPCar]

Hi, 

I need the follow formula as a measure : 

( Current Month / Last Year December ) - 1 = Result 

The idea is I can compare i.e. the data for march 2001 vs december 2000, or september 2005 vs december 2004. Every Month I select vs its last year december. All in the same table.  How can I do that? 

Thanks for your help. 

[Ashish_Mathur · Accepted solution]

Hi,

Try this

=(SUM(Data[Amount])/CALCULATE(SUM(Data[Amount]),DATESBETWEEN(Calendar[Date],DATE(YEAR(MIN(Calendar[Date]))-1,12,1),DATE(YEAR(MIN(Calendar[Date]))-1,12,31))-1

There must be a calendar table.  There should be a relatiosnhip from the Date column of the Data Table to the Date column of the Calendar Table.  In the Calendar Table, write these calculated column formulas to extract Year and month.

=YEAR(Calendar[Date])

=FORMAT(Calendar[Date],"MMMM")

In the visual you create, drag Year and Month in the Filter/slicer and select any one Year/Month.

[AlejandroPCar]

Hi @Ashish_Mathur,

That's exactly what Im looking for. Thank you very much. Happy new year.

[Ashish_Mathur]

Hi,

Happy New Year to you as well.  You are welcome.

[Rfranca]

hi michael

You can calculate the averages using CALCULATE () with FILTER and the DATAADD function.
I think that will do.

DATAADD( )

https://msdn.microsoft.com/en-us/library/ee634905.aspx

ex...

Contains :=

 calculate(

     Sum([value]),

     Filter(

         All('Date'[date]),

         Contains(

            values('Date'[date]),

            'Date'[date], date(year('Date'[]date)), month('Date'[date])-1, day('Date'[date])

                  )

            )

       )