Keep count of current events by the end of month and plot

[kuzya]

Hello Power Bi community,

I need your expertise in finding a way to plot current "Open" events by the end of each month.

I have records that were opened and closed on a given time period. By the end of the month, I need to account those currently "Open" events and plot the in a bar chart. For example, I have a total of 11 open events (opened through different periods of time, I Opened 2 events in Feb and Closed 2 Events in Feb as well. How can I count those 11 currently open events to be added in Feb and keep that count for the subsequent months (11 by end of Feb, 2 opened in march ->3 closed in march = (11+2)-3 = 10 by end of March.......)?

I created a relationship calendar to combine opened and closed dates data. I would appreciate your support as I tried multiple options to account those open events, but it plots them in the date they were created and not at the end of the month.

[amitchandak · Accepted solution]

@kuzya , refer if this can help

Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[kuzya]

@amitchandak ,

Thank you so much for your guideance and for your contributions in this community! The video explained clearly what I was looking for! 

If someone wants to know the DAX formula is:

Current Count = 

 CALCULATE(COUNTX(FILTER('Table','Table'[date_opened] <= MAX('Relationship Dates'[Date]) && (ISBLANK('Table'[date_closed]) || 'Table'[date_closed] > MAX('Relationship Dates'[Date]))),'Table'[Event_id]),CROSSFILTER('Relationship Dates'[Date],'Table'[date_opened],None))

Output:

Cheers!