User Profile
zameenakarmali
Helper I
Joined 1 year ago
User Widgets
Contributions
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 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
