Forum Discussion

zameenakarmali's avatar
zameenakarmali
Helper I
1 year ago

Calculating ARR

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 N...
bhanu_gautam's avatar
bhanu_gautam
Super User
1 year ago

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's avatar
zameenakarmali
Helper I
1 year ago

Thank you for this, 

 

I got the following error: 

 

any ideas? 

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    1 year ago

    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's avatar
      zameenakarmali
      Helper I
      1 year ago

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