Calculating ARR

[zameenakarmali]

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

[v-prasare]

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

[v-prasare]

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

[v-prasare]

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

[bhanu_gautam]

@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.

[zameenakarmali]

Thank you for this, 

I got the following error: 

any ideas? 

[bhanu_gautam]

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

[zameenakarmali]

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