Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
kuzya
Frequent Visitor

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

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

kuzya_0-1677620218598.png

 

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.

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

@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:
kuzya_0-1677687929279.png

Cheers!

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.