Forum Discussion
Count over duration
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?
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
3 Replies
Anonymous , refer if my blog or video can help
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.
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?
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
