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

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
VD
Frequent Visitor

Count of each month by year

I have two dates as created date and closed date.

sample data as below,

Created DateClosed Date
20 February 20192 March 2019
21 February 201910 March 2019
1 March 20204 April 2020 
1 January 20212 February 2021
10 January 202123 February 2021
2 February 20215 March 2021

 

I calculated count of each month by year as below,

 

Created DateClosed DateCreated Date CountClosed Date Count
20 February 20192 March 201922
21 February 201910 March 201922
1 March 20204 April 2020 11
1 January 20212 February 202122
10 January 202123 February 202122
10 Februray 202123 March 202111

 

When I'm trying to create graph, I am passing Created Date and Closed Date in Axis and in Values i'm passing created date count, closed date count, but on graph not showing the correct count because i'm passing 2 different date's in Axis.

 

I have tried using Calendar table also but while creating relationship it giving me an error: Circular dependancy was detected.

 

Is there any other way to do it, without using UseRelationship Function.

4 REPLIES 4
amitchandak
Super User
Super User

@VD , refer to my HR blog if that can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

@amitchandak is it possible to do this without creating Active or inactive relationships?

@VD , Try with an independent Table

 

Created =
var _min = minx(allselected(Date), Date[Date])
var _max = maxx(allselected(Date), Date[Date] )
return
calculate(sum(Table[Value]), filter(Table, Table[Created Date] <=_max && Table[Created Date] >=_min))

 

Closed =
var _min = minx(allselected(Date), Date[Date])
var _max = maxx(allselected(Date), Date[Date] )
return
calculate(sum(Table[Value]), filter(Table, Table[Closed Date] <=_max && Table[Created Date] >=_min))

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

@amitchandak I tried, but not working, still not showing the correct count.

Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

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