topic Re: Count of each month by year in DAX Commands and Tips

Count of each month by year

VD — Mon, 01 Mar 2021 12:38:28 GMT

I have two dates as created date and closed date.

sample data as below,

Created Date	Closed Date
20 February 2019	2 March 2019
21 February 2019	10 March 2019
1 March 2020	4 April 2020
1 January 2021	2 February 2021
10 January 2021	23 February 2021
2 February 2021	5 March 2021

I calculated count of each month by year as below,

Created Date	Closed Date	Created Date Count	Closed Date Count
20 February 2019	2 March 2019	2	2
21 February 2019	10 March 2019	2	2
1 March 2020	4 April 2020	1	1
1 January 2021	2 February 2021	2	2
10 January 2021	23 February 2021	2	2
10 Februray 2021	23 March 2021	1	1

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.

Re: Count of each month by year

amitchandak — Mon, 01 Mar 2021 13:40:29 GMT

VD , refer to my HR blog if that can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

Re: Count of each month by year

VD — Mon, 01 Mar 2021 15:07:15 GMT

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

Re: Count of each month by year

amitchandak — Mon, 01 Mar 2021 15:30:31 GMT

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

Re: Count of each month by year

VD — Tue, 02 Mar 2021 06:10:22 GMT

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