Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- number of days active between dates based on an ov...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

02-07-2018
09:02 AM

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!

Thanks,

Jacob

Solved! Go to Solution.

1 ACCEPTED SOLUTION

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-07-2018
01:43 PM

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...

Message 3 of 3

1,239 Views

2 REPLIES 2

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-07-2018
11:39 AM

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&¤t_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...

Message 2 of 3

1,043 Views

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-07-2018
01:43 PM

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...

Message 3 of 3

1,240 Views

Featured Topics

Top Solution Authors

User | Count |
---|---|

340 | |

149 | |

85 | |

79 | |

44 |

Top Kudoed Authors

User | Count |
---|---|

399 | |

239 | |

112 | |

110 | |

97 |