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]) )
  )
BetterCallFrank's avatar
BetterCallFrank
Icon for Resolver IV rankResolver IV
9 years ago

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'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
    Icon for Community Champion rankCommunity 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]) )
  )