Power BI Measure Calculation

[Simonc64]

Hi all Im relatively new to PBI but know my way around quite well, but this has had me stumped all day!

I have a dataset of say 700 instances of client ativity over the last 12 months. each client has a unique id, each activity has a start and end date and each client could have several activities but only one can be active at a time, or they may have no active activities at all. i need a power bi measure to show me the number of open activities at the end of each month for the year 2024. The placement_start date must be before each month end, the placement_end date must be null OR after a month end and the client age must be under 18 at the end of a month. so for example one activity could span April to July where i would want to count the unique identifier, but then nothing from August onwards. I have a calendar table which calculates EOMONTH for reference, but its the monthly count that keeps failing. Open to any approach that would solve my issue. in reality i just a crosstab visual with months ends as columns and the count as the values - been a very frustrating day !

 

[Matt Allington]

This is a tricky problem in Power BI because there are 2 date columns. Power BI is really good at picking a single date and analysing the transactions on that (filtered) date. Filtering between 2 dates is a lot harder.

I wrote this article some time ago. How Many Employees by Period

It’s not the same problem, but it’s similar. My blog shows how many employees are active between 2 dates. Your problem is similar; how many open activities between 2 dates. Take a look and see if it helps m

 

[Simonc64]

Thanks Matt, I will have a look through - ok to message you if i have a question on your article?

This is a tricky problem in Power BI because there are 2 date columns. Power BI is really good at picking a single date and analysing the transactions on that (filtered) date. Filtering between 2 dates is a lot harder.

I wrote this article some time ago. How Many Employees by Period

It’s not the same problem, but it’s similar. My blog shows how many employees are active between 2 dates. Your problem is similar; how many open activities between 2 dates. Take a look and see if it helps m

 

[Simonc64]

This is a tricky problem in Power BI because there are 2 date columns. Power BI is really good at picking a single date and analysing the transactions on that (filtered) date. Filtering between 2 dates is a lot harder.

I wrote this article some time ago. How Many Employees by Period

It’s not the same problem, but it’s similar. My blog shows how many employees are active between 2 dates. Your problem is similar; how many open activities between 2 dates. Take a look and see if it helps m

Thanks for sharing that article Matt it was incredibly helpful and clear. Also the example you used gives me a tantalisingly close outcome to that that i am looking for. The calculation is all that appears to be out, but i have another variable i need to work into my measure, and that is whether the client was u18 or not, hopefully that will fix the issue - I will report back!