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]) )
  )
jsquaredz's avatar
jsquaredz
Icon for Advocate I rankAdvocate I
9 years ago

Could you just do a calculated field that says

 

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

DarioSantos's avatar
DarioSantos
Frequent Visitor
9 years ago

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

I have the datedimension table with all day.

 

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