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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Hammerjunge
New Member

Line Chart: AND Logic

Dear community,

 

I am building a dashboard for my team to summarize our work intake and output. We manage our tasks in a backlog in a SharePoint list. Each entry (work item) consists of multiple fields such as Name, Description, Entry Date, Close Date, etc...

 

I'm trying to create a single visual that shows a "burndown" of how many entries are created and how many are completed for each month. But so far I have only been able to this by creating 2 separate visuals - one, where the x-axis is based on the entry date, and one where the x-axis is based on the closed date. See below:

Hammerjunge_0-1720647179969.png

 

Here's some sample data that can be used for the visual:

Work ItemEntry DateClose Date
Task 13/28/20244/22/2024
Task 23/28/20244/26/2024
Task 33/27/20245/13/2024
Task 44/17/20244/18/2024
Task 54/17/20243/29/2024
Task 65/21/20245/21/2024
Task 76/11/20246/18/2024
Task 86/18/20247/3/2024
Task 96/18/20246/25/2024
Task 106/18/20246/25/2024
Task 116/18/20246/25/2024
Task 126/18/20246/25/2024
Task 136/18/20246/25/2024
Task 146/25/20247/1/2024
Task 157/2/20247/15/2024
Task 167/9/20247/10/2024
Task 176/12/20233/8/2024
Task 189/19/20233/22/2024
Task 1911/10/20234/5/2024
Task 201/8/20246/17/2024
Task 211/8/20244/12/2024
Task 221/12/20243/1/2024
Task 231/31/20243/8/2024
Task 242/12/20243/8/2024
Task 252/12/20242/14/2024
Task 262/20/20243/8/2024
Task 272/22/20243/14/2024
Task 282/22/20242/27/2024
Task 292/26/20243/20/2024
Task 302/28/20243/12/2024
Task 312/29/20243/20/2024
Task 322/29/20244/11/2024
Task 332/29/20243/21/2024
Task 342/29/20243/27/2024
Task 352/29/20243/21/2024
Task 362/29/20243/29/2024
Task 372/29/20244/9/2024
Task 383/11/20243/13/2024
Task 393/11/20243/13/2024
Task 403/11/20244/12/2024
Task 413/11/20243/29/2024
Task 423/11/20243/26/2024
Task 433/12/20246/18/2024
Task 443/14/20243/20/2024
Task 453/14/20243/25/2024
Task 463/20/20244/9/2024
Task 473/26/20245/1/2024
Task 483/26/20244/9/2024
Task 493/28/20245/17/2024
Task 503/21/20243/25/2024
Task 513/21/20243/29/2024
Task 523/21/20243/25/2024
Task 533/27/20244/18/2024
Task 543/28/20244/8/2024
Task 553/29/20245/10/2024
Task 564/4/20244/4/2024
Task 574/2/20244/4/2024
Task 584/9/20244/25/2024
Task 594/10/20245/14/2024
Task 604/10/20245/15/2024
Task 614/10/20244/19/2024
Task 624/11/20244/15/2024
Task 634/11/20244/29/2024
Task 644/12/20245/3/2024
Task 654/16/20244/17/2024
Task 664/17/20244/19/2024
Task 674/17/20244/18/2024
Task 684/17/20244/19/2024
Task 694/17/20244/19/2024
Task 704/19/20244/19/2024
Task 714/19/20246/21/2024
Task 724/22/20245/3/2024
Task 734/22/20245/13/2024
Task 744/22/20244/22/2024
Task 754/22/20245/23/2024
Task 764/22/20245/17/2024
Task 774/22/20245/16/2024
Task 784/22/20245/17/2024
Task 794/22/20245/17/2024
Task 804/22/20245/15/2024
Task 814/23/20244/24/2024
Task 824/25/20245/6/2024
Task 834/26/20245/23/2024
Task 844/23/20245/21/2024
Task 854/23/20245/21/2024
Task 864/29/20244/30/2024
Task 874/30/20245/13/2024
Task 884/26/20245/14/2024
Task 894/30/20245/10/2024
Task 905/6/20245/8/2024
Task 915/7/20246/3/2024
Task 925/8/20245/8/2024
Task 935/8/20245/20/2024
Task 945/7/20245/21/2024
Task 955/7/20245/31/2024
Task 965/13/20245/22/2024
Task 975/13/20245/23/2024
Task 985/13/20245/21/2024
Task 995/13/20245/22/2024
Task 1005/13/20245/21/2024

 

Could you help me figure out how I can create a visual with both data point that show: Entered and closed items each month, where closed items were created that month AND were created in a month prior. Based on the sample data, the visual should show:

June 2023: 1 created

September 2023: 1 created

November 2023: 1 created

January 2024: 4 created

February 2024: 14 created, 2 closed

March 2024: 21 created, 24 closed

April 2024: 36 created, 27 closed

May 2024: 12 created, 33 closed

June 2024: 8 created, 10 closed

July 2024: 2 created, 4 closed

 

Thank you in advance.

3 REPLIES 3
v-jtian-msft
Community Support
Community Support

Hi,@Hammerjunge I am glad to help you.
Hello,@lbendlin. thanks for your concern about this issue.

Your answer is excellent! And I would like to share some additional solutions below.

If you have found suitable solutions, please share them as it will help more users with similar problems.
Or you can mark the valid suggestions provided by other users as solutions
If your requirements have not yet been realized, I have performed the relevant tests based on the data you have since provided, which I hope will be helpful to you.

According to your description, you want to see how many work items are opened and closed per month.
Here is my test

vjtianmsft_0-1721284393783.png

vjtianmsft_1-1721284400574.pngvjtianmsft_2-1721284409681.png

vjtianmsft_3-1721284417954.pngvjtianmsft_4-1721284431675.png

M_EntryNum = 
CALCULATE (
    COUNTROWS ( 'Table' ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Entry Date] >= MIN ( 'Calendar_'[Date] )
            && 'Table'[Entry Date] <= MAX ( 'Calendar_'[Date] )
    )
)
M_CloseNum = 
CALCULATE (
    COUNTROWS ( 'Table' ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Close Date] >= MIN ( 'Calendar_'[Date] )
            && 'Table'[Close Date] <= MAX ( 'Calendar_'[Date] )
    )
)

Here is the test data:

vjtianmsft_6-1721284544585.png

vjtianmsft_7-1721284551468.png

Calendar_ = CALENDARAUTO()

vjtianmsft_8-1721284570346.png

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

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

lbendlin
Super User
Super User

This is a standard USERELATIONSHIP pattern.

 

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

Hi @lbendlin,

 

Thank you for your reply. I have attached some sample data as well as the expected outcome in my original post. I hope this is helpful. Thanks!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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