Running Total of Open Questions (Similar to Open Tickets)

[anonymoususer92]

Hello,

I'm sure users have asked this question before, and I apologize if that is the case. However, I couldn't find any solution on the community that could satisfy my need. 

Few things to keep in mind -

1. The table, All Answers, has no Primary/Secondary Key. It has a list of questions that correspond to projects.

2. I have a Calendar Table that is not connected to the main table. I was hoping I can use the Date field from the Calendar table without being connected to the main table (it's hard to connect so many dates from the main table to the calendar table)

I am trying to graph how many questions are/were still Overdue on a given day/month/year. For a question to be overdue, it has to be Open Beyond DueDate. i.e, SelectedDate >DueDate && ISBLANK(CompletionDate)

Below is the structure - 

MCEID | DueDate      | CompletionDate | IsApplicable? |

1          | 01/15/2022 |     01/18/2022     |        Yes        

1          | 01/17/2022 |     01/20/2022     |        Yes        

2          | 01/29/2022 |     02/09/2022     |        NA 

3          | 02/01/2022 |     02/20/2022     |        No

4          | 02/15/2022 |                             |        NA

5          | 02/15/2022 |                             |       Yes

6          | 02/15/2022 |                             |       Yes

Based on the table above,

1. If a user clicked on 17th Jan on a line chart, the count should be 2.

2.If the user clicked on 18th Jan, the count should be 1.

3. If the user clicked on 21st Jan, the count should be 0.

4. If the user clicked on 16th Feb, the count should be 4, etc.

This is what I have tried and it gives me a blank value - 

Running Total = 
VAR SelectedDate = 
    SELECTEDVALUE('Calendar'[Date])
Return 
    CALCULATE(
        COUNT('All Answers'[DueDate]), FILTER('All Answers', SelectedDate > 'All Answers'[DueDate]  && ('All Answers'[CompletionDate] > SelectedDate || ISBLANK('All Answers'[CompletionDate]))))

Please let me know if I need to share a PBI file for this?

Thanks in advance,

[anonymoususer92]

@amitchandak Thank you! I am getting values now. Will verify and mark as accepted solution!

Btw, how did I get a value becuase of the keyword MAX?

[amitchandak]

@anonymoususer92 , try like

Running Total =
VAR SelectedDate =
Max('Calendar'[Date])
Return
CALCULATE(
COUNT('All Answers'[DueDate]), FILTER('All Answers', SelectedDate <= 'All Answers'[DueDate] && ('All Answers'[CompletionDate] >= SelectedDate || ISBLANK('All Answers'[CompletionDate]))))

Same as open employee without crossfilter

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...