How to calculate active subscriptions with an initial and final date

[DarioSantos]

Hi,

I have a table with subscriptions, in this table i have inicial date and final date.

How i can for with day calculate the active subcription?

In this table I have more information about the subscription, for exemple the type. I need this active subsciption can be filter for this type.

For example:

Inicial Date	Final Date	Type
01-dez	04-dez	a
02-dez	04-dez	b
05-dez	27-dez	a
10-dez	30-dez	a

Best Regards,

Dário Santos

[Baskar]

Cool .

Create one measure : 

Measure = Calculate ( DATEDIFF(Inicial Date,Final Date , DAY), Filter ( Table Name , Type = "A" )

Try this one it will give u the days between intial date to final date with type is Active .

Chnage Table and column name in the measure .

Let me know it doesn't help u . i will help u 

[jsquaredz]

Could you just do a calculated field that says

IsActive = if (Final Date >= now(), True(), False()

[DarioSantos]

I need for each day the nº of active subscriptions.

I have the datedimension table with all day.

[BetterCallFrank]

Hi Dario,

you can try a calculated measure in your date table, sth along the lines of:

NumSubscriptions = 
  COUNTROWS(
    FILTER( ALL( data ), data[start] <= MIN( 'Day'[Day] ) && data[end2] > MAX( 'Day'[Day] ) )
  )

You can find a demo PBIX here

HTH,

Frank

[DarioSantos]

Hi,

With new column in datedimension table.

NumSubscriptions = 
  COUNTROWS(
    FILTER( Table1, Table1[InicialDate].[Date] <= datedimension[date] && Table1[FinalDate] >= dateimension[date] )
  )

With one column for type and 1 line for each type I can add one more filter to do this.

My problem is this table in future is very big, if i can make this with a mesure is better.

Best Regards,

Dário Santos

[ImkeF · Accepted solution]

Hi Dario,

if you add a MIN and MAX to your "NumSubscriptions"-expression, it should work as a measure instead of a column (provided there is no (!) connection to the datedimension-table !):

NumSubscriptions = 
  COUNTROWS(
    FILTER( Table1, Table1[InicialDate].[Date] <= MIN(datedimension[date]) && Table1[FinalDate] >= MAX(dateimension[date]) )
  )