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 NameClient NoContract NoMonth AddedStarting QuarterEnd QuarterSubscription TypeReport Frequency Per YearMarketACV
Client A100100-1Jan-24Q1 2024Q4 2024One Year subscriptionQuarterlyItaly12000
Client A100100-2Sep-24Q3 2024Q2 2025One Year subscriptionQuarterlyFrance12000
Client B8888-1Apr-24Q2 2024Q1 2026Two Year SubscriptionQuarterlySpain24000
Client C9999-1Aug-23Q3 2023Q2 2024One Year SubscriptionBi-AnnualGermany8000

 

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? 
 
 2023202420252026
 JulAugSepOctNovDecJanFebMarAprMayJunJulAugSepOctNovDecJanFebMarAprMayJunJulAugSepOctNovDecJanFebMar
       120001200012000120001200012000120001200012000120001200012000               
             120001200012000120001200012000120001200012000120001200012000         
          240002400024000240002400024000240002400024000240002400024000240002400024000240002400024000240002400024000240002400024000
 800080008000800080008000800080008000800080008000                     
                                  
                                  
ARR800080008000800080008000200002000020000440004400044000480004800048000480004800048000360003600036000360003600036000240002400024000240002400024000240002400024000

7 Replies

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

  • v-prasare's avatar
    v-prasare
    Community Support
    1 year ago

    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's avatar
    v-prasare
    Community Support
    1 year ago

    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's avatar
    v-prasare
    Community Support
    1 year ago

    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