Forum Discussion
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
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]) ) )
6 Replies
jsquaredzAdvocate I
Could you just do a calculated field that says
IsActive = if (Final Date >= now(), True(), False()
I need for each day the nº of active subscriptions.
I have the datedimension table with all day.
- BetterCallFrank
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
- Baskar
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
