SUM column based on 2 date columns

[viktork]

Hello experts,

I am trying to receive "number of open bugs"  grouped by
- project

- the end of particular period week / quarter / year 

and based on CREATED and RESOLVED dates columns.

Measure Open issues is a total count of created issues until the period end minus a total count of resolved issues until the period end. We consider the issue resolved if it has a resolution date.

The complex moment is, that we need to check both columns, CREATED and RESOLVED in order to make desicion about calculation. We should count all P1, P2, P3, P4 if

Pseudo code:

foreach date X (end of particular period) {

if date X  > date[CREATED] AND (date[RESOLVED] IS EMPTY OR date[RESOLVED]>date X)

SUM(P1);

SUM(P2);

SUM(P3);

SUM(P4);

}

[viktork]

Thank you Amit,
This is exact solution for my case. Really appreciate your help!

[amitchandak · Accepted solution]

@viktork , I have blog on HR, very similar current employee.

See if that can help

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

https://www.youtube.com/watch?v=e6Y-l_JtCq4