cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

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

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.

 

image022.jpg

 

dates table

image023.jpg

 

enrollment table

image024.jpg

 

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.

 

image025.jpg

 

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

1 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")

image028.jpg

 

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

View solution in original post

2 REPLIES 2
Frequent Visitor

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

image026.jpg

 

 

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

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

image028.jpg

 

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

View solution in original post

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors