number of days active between dates based on an overall begin and end date

[scotjn1]

Hello Measure Masters!

I have a list of assets with associated start dates and end dates (eff_dt, exp_dt) and would like to be able to count how many were "active" during specified time periods.  I'm also interested in fractional amounts, for example if an asset activates/terminates mid-month, it would count as a fraction for that month.

My data model consists of a dates table with period start and end dates for each month, an enrollment table which shows the start and end dates for each asset (and a category code, which will be relevant later), and a key table which ties these two tables together along with many other tables.  The key table essentially has a record for each asset for every month in the dates table.

dates table

enrollment table

My goal is to be able to show for each inv_smry_dt selected or displayed, how many assets were active between the period_begin_dt and period_end_dt based on the number of days that the eff_dt and exp_dt overlap.  Here is a simple example with some helper columns that I threw together in Excel to help illustrate the intent.  Ultimately, I would like to delineate the counts by the serv_category_cd, and use the resulting measures in downstream calculations (e.g. $ per active "L" asset, $ per active "U" asset, etc.).  Sample output shown below in case you don't want to open the Excel file.

I found this article, which seemed like it might be a useful place to start, but I cannot figure out how to bend it to meet my specific need.  It might not even be the right approach, but I'm grasping at straws here!

https://community.powerbi.com/t5/Desktop/Calculate-days-from-filter-overlapping-date-ranges/m-p/2168...

Thanks,

Jacob

[scotjn1]

Had some time to dig into the code in that article.  These changes have made it at least start to behave properly.

calculate_start = 
var start_Date=FIRSTDATE(dates[period_begin_dt])
var current_Start=MAX(enrollment_units[eff_dt])
return
IF([Diff]<>BLANK(),MAX(current_Start,start_Date))

calculate_end = 
var end_Date=LASTDATE(dates[period_end_dt])
var current_end=MAX(enrollment_units[exp_dt])
return
IF([Diff]<>BLANK(),MIN(current_end,end_Date))

Diff = 
var start_Date=FIRSTDATE(dates[period_begin_dt])
var end_Date=LASTDATE(dates[period_end_dt])
var current_Start=MAX(enrollment_units[eff_dt])
var current_end=MAX(enrollment_units[exp_dt])
return
IF(current_end>start_Date&&current_Start<end_Date,DATEDIFF(MAX(start_Date,current_Start),MIN(end_Date,current_end),DAY),BLANK())

Now I just can't seem to sum the values for Diff for each inv_smry_dt...

[scotjn1 · Accepted solution]

OK, I think I've got it.  Here are the intermediate steps, but I'll work on streamlining/combining them some other day since it's working for now.

This will sum the Diff values for each asset-month:

SumDiff = SUMX(enrollment_units,[Diff])

And this will calculate the total active assets for a given time period, rolling up as expected:

ActiveCount = [SumDiff]/SUMX(dates,dates[Days])

Active U Count = CALCULATE([ActiveCount],enrollment_units[serv_category_cd]="U")

Active L Count = CALCULATE([ActiveCount],enrollment_units[serv_category_cd]="L")

It's probably bad form to mark my own post as the solution, but is, so...