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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
milomilo2020
Frequent Visitor

DAX - Count in a Line Chart based on Start and Finish Date

Hello,

I have this table :

milomilo2020_1-1656596756604.png

 

I have it linked to a Calendar table through the "Created" date.

I need to Show the COUNT of ID between "Created + 30" and "Resolved"

So for example, for ID 44564, the Line chart will count 1 starting from 10/2/2022 to 15/3/2022

And for ID 44555,  the Line chart will count 1 starting from 19/3/2022 to 15/5/2022

 

How can I achieve this in a measure, taking into account that the relationship between that table and the calendar table is "Created" ?

 

Thanks

1 ACCEPTED SOLUTION
v-xiaotang
Community Support
Community Support

Hi @milomilo2020 

Thanks for reaching out to us.

>> for ID 44564, the Line chart will count 1 starting from 10/2/2022 to 15/3/2022. And for ID 44555,  the Line chart will count 1 starting from 19/3/2022 to 15/5/2022

You can try this way

vxiaotang_0-1657015007578.png

Since there is no relationship between the 'calendar' and the ID in the 'Table', it needs us to manually create a measure for each id

count1 = CALCULATE(COUNT('Table'[ID]),FILTER('Table','Table'[ID]=44564 && 'Table'[Created+30]<=MIN('calendar'[Date]) && 'Table'[Resolved]>=MIN('calendar'[Date])))
count2 = CALCULATE(COUNT('Table'[ID]),FILTER('Table','Table'[ID]=44555 && 'Table'[Created+30]<=MIN('calendar'[Date]) && 'Table'[Resolved]>=MIN('calendar'[Date])))

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-xiaotang
Community Support
Community Support

Hi @milomilo2020 

Thanks for reaching out to us.

>> for ID 44564, the Line chart will count 1 starting from 10/2/2022 to 15/3/2022. And for ID 44555,  the Line chart will count 1 starting from 19/3/2022 to 15/5/2022

You can try this way

vxiaotang_0-1657015007578.png

Since there is no relationship between the 'calendar' and the ID in the 'Table', it needs us to manually create a measure for each id

count1 = CALCULATE(COUNT('Table'[ID]),FILTER('Table','Table'[ID]=44564 && 'Table'[Created+30]<=MIN('calendar'[Date]) && 'Table'[Resolved]>=MIN('calendar'[Date])))
count2 = CALCULATE(COUNT('Table'[ID]),FILTER('Table','Table'[ID]=44555 && 'Table'[Created+30]<=MIN('calendar'[Date]) && 'Table'[Resolved]>=MIN('calendar'[Date])))

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

amitchandak
Super User
Super User

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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