cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

## Count of each month by year

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.

4 REPLIES 4
Super User
Frequent Visitor

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

Super User

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

Frequent Visitor

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