Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
brondino
New Member

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

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!screenshot.1309.jpg

1 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

 

screenshot.png

View solution in original post

4 REPLIES 4
dralexandero
Frequent Visitor

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

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:

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

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

 

screenshot.png

Thank you so much dralexandero! You nailed it!

It's exactly what I needed.

 

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.