topic Re: Calculating ARR in DAX Commands and Tips

Calculating ARR

zameenakarmali — Thu, 06 Mar 2025 11:05:43 GMT

Hi,

I would like to calculate ARR, this is a sum of the ACV for a given month-year.

I have the following Contract Table:

When the contract ends the Value is 0.

Client Name	Client No	Contract No	Month Added	Starting Quarter	End Quarter	Subscription Type	Report Frequency Per Year	Market	ACV
Client A	100	100-1	Jan-24	Q1 2024	Q4 2024	One Year subscription	Quarterly	Italy	12000
Client A	100	100-2	Sep-24	Q3 2024	Q2 2025	One Year subscription	Quarterly	France	12000
Client B	88	88-1	Apr-24	Q2 2024	Q1 2026	Two Year Subscription	Quarterly	Spain	24000
Client C	99	99-1	Aug-23	Q3 2023	Q2 2024	One Year Subscription	Bi-Annual	Germany	8000

I have created a date table as below:

Dim_Date =

VAR _CurrentDate= TODAY()

VAR _CurrentYear = YEAR(_CurrentDate)

VAR _StartYear = YEAR(_CurrentDate) - 5

VAR _EndYear = Year(_CurrentDate) + 3

VAR _StartDate = DATE(_StartYear,1,1)

VAR _EndDate = DATE(_EndYear,12,31)

VAR _calendar = CALENDAR(_StartDate,_EndDate)

RETURN

ADDCOLUMNS(

_calendar,

"Quarter", "Q" & FORMAT( [Date], "Q yyyy"),

"Month-Year", FORMAT([Date], "MMM yyyy"),

"Month No", MONTH([Date]),

"Month", FORMAT([Date],"mmmm"),

"Year",FORMAT([Date],"yyyy"),

"Qtr No", (YEAR([Date]) - _StartYear) * 4 + QUARTER([Date]))

There is a relationship from Dim_Date Month-Year and Month Added in the Contract Table,

I would like to have a slicer where you can select a select a month-year and the ARR amount is given

Below is an example of the snowball for the data

The ARR Is the sum of the ACV Values, In Power BI I would just like to eg. Select Jan 24 and it should return the ARR amount as 20000, Please can you let me know how to do this?

	2023	2024	2025	2026
	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar
							12000	12000	12000	12000	12000	12000	12000	12000	12000	12000	12000	12000
													12000	12000	12000	12000	12000	12000	12000	12000	12000	12000	12000	12000
										24000	24000	24000	24000	24000	24000	24000	24000	24000	24000	24000	24000	24000	24000	24000	24000	24000	24000	24000	24000	24000	24000	24000	24000
	8000	8000	8000	8000	8000	8000	8000	8000	8000	8000	8000	8000


ARR	8000	8000	8000	8000	8000	8000	20000	20000	20000	44000	44000	44000	48000	48000	48000	48000	48000	48000	36000	36000	36000	36000	36000	36000	24000	24000	24000	24000	24000	24000	24000	24000	24000

Re: Calculating ARR

bhanu_gautam — Thu, 06 Mar 2025 11:11:49 GMT

zameenakarmali , Create a measure using

DAX
ARR =
VAR SelectedMonthYear = SELECTEDVALUE(Dim_Date[Month-Year])
VAR SelectedDate = MAX(Dim_Date[Date])
RETURN
CALCULATE(
SUM(Contract[ACV]),
FILTER(
Contract,
Contract[Month Added] <= SelectedMonthYear &&
Contract[End Quarter] >= SelectedMonthYear
)
)

Create a Slicer: Add a slicer to your report using the Month-Year column from the Dim_Date table. This will allow you to select a specific month-year.

Re: Calculating ARR

zameenakarmali — Thu, 06 Mar 2025 11:49:53 GMT

Thank you for this,

I got the following error:

any ideas?

Re: Calculating ARR

bhanu_gautam — Thu, 06 Mar 2025 11:55:42 GMT

zameenakarmali , This is data type error

Try using

dax
ARR =
VAR SelectedDate = MAX(Dim_Date[Date])
RETURN
CALCULATE(
SUM(Contract[ACV]),
FILTER(
Contract,
Contract[Month Added] <= SelectedDate &&
Contract[End Quarter] >= SelectedDate
)
)

Re: Calculating ARR

zameenakarmali — Thu, 06 Mar 2025 12:13:41 GMT

The issue is that we are comparing the Quarters which is a text field to Month-Year which is a date column

Re: Calculating ARR

v-prasare — Fri, 09 May 2025 03:56:27 GMT

Hi zameenakarmali,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for your issue worked? or let us know if you need any further assistance here?

Thanks,

Prashanth Are

MS Fabric community support

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your query

Re: Calculating ARR

v-prasare — Wed, 14 May 2025 04:44:59 GMT

Hi @zameenakarmali,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for your issue worked? or let us know if you need any further assistance here?

Thanks,

Prashanth Are

MS Fabric community support

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your query

Re: Calculating ARR

v-prasare — Tue, 20 May 2025 13:45:19 GMT

Hi @zameenakarmali,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for your issue worked? or let us know if you need any further assistance here?

Thanks,

Prashanth Are

MS Fabric community support

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your query