Count rows based on date condition from another table

[EnSe]

Hello @ all,

I'm just about to build a report in powerBI for a Jira Project-Reporting. For this I want to display a burndown chart showing the burndown of tasks starting from the project start-date till todays day. 

Now I have 2 tables:

- Issue_list: Contains all relevant issues including the their resolution date (date/time format)

- Date_table: A simple date-Table containing all dates (days) from the project start till now

My approach was to add another column to the Date_table called "count". The function behind should be that for each date row it should count the number of rows in the Issue_list table where the following condition applies 'IssueList'[ResolutionDate] = NULL or is greater than the actual date in Date_table.

However, I have to admit that I got completely stuck at that point 😞 

Does someone of you maybe have an idea how to do this? Or maybe has completely different solution approach?

Thank you in advance for your support

Best regards

[rohit1991]

hi @EnSe ,

Solution:

1. DAX Measure:
   Count of Issues =
   COUNTROWS(FILTER(Issue_list,ISBLANK(Issue_list[ResolutionDate]) || Issue_list[ResolutionDate] > SELECTEDVALUE(Date_table[Date])))

2. This dynamically counts unresolved or future-dated issues.

3. Visualize:

   • Use Date_table[Date] on the X-axis.
   • Use Count of Issues as the Y-axis in your burndown chart.
     
     
     

please refer these links  --> https://learn.microsoft.com/en-us/dax/countrows-function-dax

[v-achippa]

Hi @EnSe,

Thank you for reaching out to Microsoft Fabric Community.

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the super user for the issue worked? or let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,

Anjan Kumar Chippa

[v-achippa]

Hi @EnSe,

Thank you for reaching out to Microsoft Fabric Community.

We wanted to kindly follow up to check if the solution provided by the super user for the issue worked.
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,

Anjan Kumar Chippa

[v-achippa]

Hi @EnSe,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the super user for the issue worked. If the issue is resolved, please mark the solution as Accept as solution to help the other members of the community find it more quickly. Don't forget to give a "Kudos" – I’d truly appreciate it! 

Thank you for being a part of the Microsoft Fabric Community Forum!

Regards,

Anjan Kumar Chippa

[johnt75 · Accepted solution]

Make sure that there is no relationship between the date table and the issues table and then you can create a measure like

Burn Down =
VAR MaxDate =
    MAX ( 'Date'[Date] )
VAR Result =
    CALCULATE (
        COUNTROWS ( 'Issues' ),
        'Issues'[Resolution Date] IN { BLANK (), MaxDate }
    )
RETURN
    Result