Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
DarioSantos
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 DateFinal DateType
01-dez04-deza
02-dez04-dezb
05-dez27-deza
10-dez30-deza

 

Best Regards,

Dário Santos

1 ACCEPTED SOLUTION

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!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

6 REPLIES 6
Baskar
Resident Rockstar
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 

jsquaredz
Advocate I
Advocate 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.

 

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

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

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!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.