cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

Compare against last year selected month

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. 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Compare against last year selected month

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

4 REPLIES 4
Rfranca Member
Member

Re: Compare against last year selected month

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

                  )

            )

       )

Super User
Super User

Re: Compare against last year selected month

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Re: Compare against last year selected month

Hi @Ashish_Mathur,

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

Highlighted
Super User
Super User

Re: Compare against last year selected month

Hi,

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 146 members 1,655 guests
Please welcome our newest community members: