Count over duration

[Anonymous]

Hello together,

I have the following problem. I have 2 columns with cases, "start date" and "resolved date". I would like to show a time course of the duration until the end over months. For example: 01.01.2021 (start) - 01.03.2021 (resolved date), then there should be a 1 in the graph from january to March, and in March then a 0, cause in March the case was closed.

Do you have any ideas to to this?

[Anonymous · Accepted solution]

Hi @Anonymous ,

Check the measures.

create = CALCULATE(DISTINCTCOUNT('Table'[id]),FILTER(ALL('Table'),MONTH('Table'[start])<=MONTH(SELECTEDVALUE('calendar'[date]))&&MONTH('Table'[resolve])>MONTH(SELECTEDVALUE('calendar'[date]))))

resolve = CALCULATE(DISTINCTCOUNT('Table'[id]),FILTER(ALL('Table'),MONTH('Table'[resolve])<=MONTH(SELECTEDVALUE('calendar'[date]))))

Best Regards,

Jay

[amitchandak]

@Anonymous , refer if my blog or video can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

or

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

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Anonymous]

Hi @amitchandak ,

thank you for the help but it still doesnt work with that measures.

In this example, it started in January and finished in March. I should see a 1 in January an February until March. If I create also a measure to show the "resolved" ones, there would bei Created 1 in Jan 1 in Feb and resolved 1 in March.

Do you have any ideas for this use case?