Calculate revenue forecast in current calendar year

[arnomics]

Hi all,

I have a tricky calculation that I'm trying to get my head around on. 

I have 3 tables - Service, Subscription and Date (Date is a Directquery from DB & is not marked as a Date table)

Service table has the Column Service ID, Monthly Price & Service Status (I'm only interested in Active or Inactive status)

Subscription table has the Service Start Date & Service End date (for inactive services)

I'm trying to create a Measure that will help me bring in the Calendar Year (with all months in current year) so that I can show total sum of all Active services + Sum of services cancelled in a given month. Also the ability to show them as cumulative over months.

The problem I have is the Service ID has a one-off Total Price, it is meant to be monthly recurring charge until the service is made inactive.

I've created the below Measure that kind of flags all the service IDs that are either still active or were active at some point of time in current year (i.e services that were cancelled in the current year). But I want to take it to the next step now and use these Service IDs to create a table visual that shows me monthly recurring charge over the current year. 

Any help appreciate. Thanks. 

Active in Current Year =
CALCULATE (
    MAX ( SERVICE[SERVICE_ID] ),
    TREATAS ( { "Active", "Inactive" }, 'SERVICE'[SERVICE_STATUS] ),
    SERVICE[MONTHLY_PRICE] > 0,
    SERVICE[CANCEL_DATE] >= DATE ( YEAR ( TODAY () ), 2, 1 )
)

[Ashish_Mathur]

Hi,

Share the tables in a format that can be pasted in an MS Excel file.

[arnomics]

Hi @Ashish_Mathur 

Thanks for your help. Please see below. 

The challenge for me with DAX measure as I mentioned is because I want to show the list of services that were active in the Current Year or had at least 1 billable day. But I want to use the Price as a Table visual that shows Revenue generated by services in Current Year by month (i.e Jan-Dec). I

Service Table

Service ID	Price (per month)	Service Status
1	$50	Active
2	$25	Active
3	$40	Active
4	$50	Inactive
5	$25	Active
6	$40	Active
7	$50	Active
8	$25	Active
9	$40	Active
10	$50	Active
11	$25	Inactive
12	$40	Inactive
13	$50	Inactive
14	$25	Active
15	$40	Active
16	$50	Inactive
17	$25	Inactive
18	$40	Inactive

Subscription Table

Service ID	Service Activation Date	Service End Date
1	1/02/2002
2	1/09/2010
3	2/02/2019
4	1/06/2002	1/12/2016
5	1/06/2010
6	2/12/2019
7	1/01/2020
8	12/12/2005
9	2/02/2019
10	1/06/2002
11	1/06/2010	1/07/2015
12	2/12/2019	23/02/2023
13	1/02/2002	1/04/2023
14	1/09/2010
15	2/02/2019
16	8/08/2011	1/06/2023
17	9/08/2017	9/05/2023
18	1/01/2023	30/03/2023

[Ashish_Mathur]

Hi,

Please find attached my solution.

Hope this helps.

[arnomics]

Thanks @Ashish_Mathur  for taking the time to help out.

I just have one problem with this. I'm using a published dataset & as a result cannot create a Calculated Column, like you provided with the solution. 

The Subscription table had this Calculated Column - 

Price = RELATED(Service[Price (per month)])

With my Model I can only create Measures.  When I try to replicate the solution you helped with, I get the same number across different months. I'm guessing this is because there is no relationship. 

[lbendlin]

I'm guessing this is because there is no relationship. 

Please show your data model.

[Ashish_Mathur]

You are welcome.  Someone else will help you with this.

[lbendlin]

SUMMARIZECOLUMNS over Service_ID and Month, and add a measure that pulls in the monthly revenue if the service_id was active in that month.  Then SUMX the result. 

[arnomics]

That could work but I don't know how to show the Price in Current Year as the Price is stored at the Service ID Row level which has an Activation date (Which was in the past). Whereas I'm trying to use this information to show on a table visual what revenue has been generated by Active services in the current year by Month.