COUNTROWS with specific date conditions throughout the year (accumulated indicator on line chart)

[brondino]

Hi there,

Please, help me to create a measure (DAX) and a line chart that shows the following:

1. If a select the month "Janeiro" (january), I want to show in the line chart the number of notifications in my fCMPP_Notificacoes table issued on january/2018 (column "Data da Notificacao") and concluded on january/2018 (column "Data da Conclusao") DIVIDED by the number of notifications issued on january/2018.

2. If a select the months "Janeiro" (january) AND "Fevereiro" (february), I want to show in the line chart the number of notifications in my fCMPP_Notificacoes table issued on 2018 up to february/2018 (column "Data da Notificacao") and concluded on 2018 up to february/2018 (column "Data da Conclusao") DIVIDED by the number of notifications issued on 2018 up to february/2018. The january/2018 value explained on item 1 must also be shown on the chart.

3. 2. If a select the months "Janeiro" (january) AND "Fevereiro" (february) AND "Março" (march), I want to show in the line chart the number of notifications in my fCMPP_Notificacoes table issued on 2018 up to march/2018 (column "Data da Notificacao") and concluded on 2018 up to march/2018 (column "Data da Conclusao") DIVIDED by the number of notifications issued on 2018 up to march/2018. The january/2018 AND february/2018 values explained on items 1 and 2 must also be shown on the chart.

4. And so on (I think you got the idea).

Additional information:
Table: fCMPP_Notificacoes (information about the issued and concluded notifications throughout the year, each notification is represented in a row)
Notification issue date column: fCMPP_Notificacoes[Data da Notificacao]
Notification conclusion date column: fCMPP_Notificacoes[Data da Conclusao]

Something like this:

Thanks in advance!

[dralexandero]

Hi @brondino, I'm interested in this problem, could you please also post a sample dataset for creating the measure?

[brondino]

Glad you're interested!

There is also a condition that I didn't mention before for a notification to be considered concluded. The column "Status" must be "Treated concluded".

Here are the sample data set:

Code	Status	Data da Notificacao	Data da Conclusao
CMPP-2018-00001	Treated concluded	01/02/2018	01/23/2018
CMPP-2018-00002	Treated concluded	01/02/2018	01/03/2018
CMPP-2018-00014	Treated concluded	01/05/2018	01/08/2018
CMPP-2018-00017	In progress	01/04/2018
CMPP-2018-00018	Treated concluded	01/08/2018	01/09/2018
CMPP-2018-00019	Untreated concluded	01/08/2018	01/09/2018
CMPP-2018-00020	Treated concluded	01/08/2018	02/06/2018
CMPP-2018-00022	Treated concluded	01/09/2018	01/10/2018
CMPP-2018-00039	Treated concluded	01/16/2018	02/22/2018
CMPP-2018-00044	Treated concluded	01/18/2018	02/08/2018
CMPP-2018-00045	Untreated concluded	01/19/2018	01/23/2018
CMPP-2018-00046	Treated concluded	01/19/2018	01/22/2018
CMPP-2018-00047	Treated concluded	01/22/2018	02/08/2018
CMPP-2018-00052	In progress	01/23/2018
CMPP-2018-00053	Treated concluded	01/23/2018	03/19/2018
CMPP-2018-00055	Treated concluded	01/24/2018	02/08/2018
CMPP-2018-00066	Treated concluded	01/30/2018	02/01/2018
CMPP-2018-00068	Treated concluded	01/30/2018	03/20/2018
CMPP-2018-00069	Treated concluded	01/30/2018	01/30/2018
CMPP-2018-00070	Untreated concluded	01/30/2018	03/07/2018
CMPP-2018-00071	Treated concluded	01/31/2018	02/08/2018
CMPP-2018-00125	Treated concluded	02/27/2018	02/27/2018
CMPP-2018-00127	Treated concluded	02/28/2018	03/09/2018
CMPP-2018-00128	In progress	02/28/2018
CMPP-2018-00129	Treated concluded	02/28/2018	03/16/2018
CMPP-2018-00130	Treated concluded	03/01/2018	03/05/2018
CMPP-2018-00136	Treated concluded	03/05/2018	03/07/2018
CMPP-2018-00138	In progress	03/05/2018
CMPP-2018-00140	Treated concluded	03/07/2018	03/09/2018
CMPP-2018-00142	Treated concluded	03/07/2018	03/16/2018
CMPP-2018-00143	Treated concluded	03/07/2018	03/12/2018
CMPP-2018-00155	Untreated concluded	03/13/2018	03/14/2018
CMPP-2018-00157	Treated concluded	03/15/2018	03/16/2018
CMPP-2018-00158	Treated concluded	03/07/2018	03/16/2018
CMPP-2018-00163	Treated concluded	03/19/2018	03/20/2018

[dralexandero · Accepted solution]

Hi @brondino, is this what you're looking for? The single selection is on, so if you select 3, Jan - Mar are all selected.

https://drive.google.com/file/d/14JyYIRvZJ7lsUGfY9DYVfUOtkesqWLl5/view?usp=sharing

[brondino]

Thank you so much dralexandero! You nailed it!

It's exactly what I needed.