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

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

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

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

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors