The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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.
Solved! Go to Solution.
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
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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
101 | |
81 | |
62 | |
54 |
User | Count |
---|---|
249 | |
119 | |
115 | |
94 | |
70 |