Forum Discussion

DarioSantos's avatar
DarioSantos
Frequent Visitor
9 years ago
Solved

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...
Need Help
  • ImkeF's avatar
    ImkeF
    9 years ago

    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]) )
  )
DarioSantos's avatar
DarioSantos
Frequent Visitor
9 years ago

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's avatar
ImkeF
Community Champion
9 years ago

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