cancel
Showing results for
Did you mean:
Frequent Visitor

## How to calculate active subscriptions with an initial and final date

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

1 ACCEPTED SOLUTION
Super User

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]) )
)```

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

6 REPLIES 6
Resident Rockstar

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

Could you just do a calculated field that says

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

Frequent Visitor

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

I have the datedimension table with all day.

Resolver IV

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

Frequent Visitor

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

Super User

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]) )
)```

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

Announcements

#### Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

#### Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

#### What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.