Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
kpangelinan
Helper I
Helper I

Calculate sum for specific period and difference between same period of previous year

How do I calculate revenue from one month to the same month in the previous year?

 

I have an ARR column and would like to calculate the sum for a particular period (e.g. Jan 2016) and calculate the difference in ARR between that period and the same period of the previous year (i.e. Jan 2017 vs Jan 2016).

Executed Agreement Date

ARR Amount

1/30/2017

1590.5

1/30/2017

381.72

1/12/2016

31.81

1/30/2016

2226.7

 

I also created another table with a datekey for dates between 2000-2030 and columns for year and month – in case that’s needed in a formula.

 Capture1.PNG

 

 

 

 

 

 

 

 

 

 

 

1 ACCEPTED SOLUTION
CheenuSing
Community Champion
Community Champion

hi @kpangelinan

 

Try the following

 

Assumptions:  The calendar table and the dataTable are linked on the date columns of respective tables.

                        You also have a column in Calendar Table for YearMonth

 

1. Create a measure called  ARRTotal = sum(datatable[ARRAmount]).

 

2. For Previous Year Same Period create the measure

   ARRPrYr = Calculate([ARRTotal],SAMEPERIODLASTYEAR('Calendar'[Date]))

 

3. Now plot a Table visual with 

   a) YearMonth column from calendar table

   b) Measure ARRTotal

   c) Measure ARRPrYr

 

You should be able to see the result you expect.

 

If this solves your issue please accept this as a solution and also give KUDOS.

 

Cheers

 

CheenuSing

 

 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

View solution in original post

2 REPLIES 2
CheenuSing
Community Champion
Community Champion

hi @kpangelinan

 

Try the following

 

Assumptions:  The calendar table and the dataTable are linked on the date columns of respective tables.

                        You also have a column in Calendar Table for YearMonth

 

1. Create a measure called  ARRTotal = sum(datatable[ARRAmount]).

 

2. For Previous Year Same Period create the measure

   ARRPrYr = Calculate([ARRTotal],SAMEPERIODLASTYEAR('Calendar'[Date]))

 

3. Now plot a Table visual with 

   a) YearMonth column from calendar table

   b) Measure ARRTotal

   c) Measure ARRPrYr

 

You should be able to see the result you expect.

 

If this solves your issue please accept this as a solution and also give KUDOS.

 

Cheers

 

CheenuSing

 

 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

@CheenuSing That worked perfectly! Thank you very much for your help.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.