Count the number of projects with a report missing each month

[salexa]

Hi,

I have 2 tables: Projects(columns ProjectID, Status,Title)  and ProjectReports(columns ReportID, ProjectID, ReportDate, and some columns with details for each report), linked one to many - for each project we can have multiple reports.

Project Owners should add at least one report each month for a project with Status<>Closed, so I need to count projects that don't have a report added each month.

I've added some sample data for table Project:

D	Title	Status
1	TestPowerBI	In Progress
2	TestVisio	Closed
3	Visual	In Progress

For table ProjectReports:

ReportID	ProjectId	ReportDate
1	1	06.06.2020
2	1	09.06.2020
3	1	01.07.2020
4	1	15.08.2020
5	1	11.10.2020
11	2	01.01.2020
13	2	05.02.2020
18	2	15.03.2020
22	2	22.04.2020
35	3	05.01.2020
36	3	05.02.2020
37	3	05.03.2020
38	3	16.05.2020
39	3	22.06.2020
40	3	01.07.2020
41	3	02.08.2020
42	3	05.10.2020

For eg, Project 3, doesn't have a report added in April and September

Could you help please? Should I create a separate table that adds a line for each month? Or is it another way to count it?(My original tables are SharePoint Tables) I've run out of ideas..

Thanks in advance

[salexa]

Hi @Anonymous ,

Yes, the month appears now, but it's not counting the project in progress, we have 0 IDs  for September( instead we hould have 2)

Thanks,

Irina

[Anonymous]

Hi @salexa ,

According to my understand, you want to count  months that have no reports under each Project whose status is Closed , right?

You could use the following formula after adding a Month column:

Measure =
VAR _monthPeriod =
    CALCULATE (
        MAX ( 'ProjectReports'[Month] ) - MIN ( 'ProjectReports'[Month] ),
        FILTER (
            'ProjectReports',
            'ProjectReports'[ProjectId] = MAX ( 'ProjectReports'[ProjectId] )
        )
    ) + 1
RETURN
    IF (
        MAX ( 'Projects'[Status] ) <> "Closed",
        _monthPeriod
            - CALCULATE (
                DISTINCTCOUNT ( ProjectReports[Month] ),
                FILTER (
                    'ProjectReports',
                    'ProjectReports'[ProjectId] = MAX ( 'ProjectReports'[ProjectId] )
                )
            )
    )

My visualization looks like this:

Here is the pbix file.

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.

Best Regards,
Eyelyn Qin

[salexa]

Hi @Anonymous ,

Thanks for your answer, it solves this part of business request.

Now I need to be able to have a column chart that counts all projects that are on going each month(Status is not Closed) even if the projects don't have a report added(in ProjectReports). Could you advice?

Thanks,

Irina

[Anonymous]

Hello @salexa ,

It seems simple.

https://qiuyunus-my.sharepoint.com/:u:/g/personal/stephent01_qiuyunus_onmicrosoft_com/EX6GeFNPIsNGtR...

But it's an additional question that's off topic...

Could you tell me if your original problem has been solved? If so, here's the solution. Thank you

Best regards
Eyelyn Qin

[salexa]

Hi @Anonymous ,

September month is missing from the chart, IDss 3 and 1 are still in Progress, but don't have a report added for september.

This is what I'm struggling with, be able to count project even if no report is available for a month.

Thanks in advance,

Irina

[Anonymous]

Hi @salexa ,

You need to enter a new table with whole month name and number, and then add a new column using the following formula which matches the existed mouths in ProjectReport table like this:

Month =
IF (
    [No] >= MIN ( 'ProjectReports'[Month] )
        && [No] <= MAX ( 'ProjectReports'[Month] ),
    [No],
    BLANK ()
)

Then make a visual shown below after building a relationship between two tables:

Here is the pbix file.

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.

Best Regards,
Eyelyn Qin

[amitchandak]

@salexa , Try a measure like

measure =
var _tab= filter(summarize(ProjectReports,Project[Title], ProjectReports[Month Year], "_1", count(ProjectReports[ReportID]), "_2",count(ProjectReports[ReportID])+0), isblank(Title))
return
countx(values(_tab[Title]), max(_tab[Title]))

[salexa]

@amitchandak  

I've tried to reproduce your logic, but for eg f I cerate a table with  your formula filter(summarize(ProjectReports,Project[Title], ProjectReports[Month Year], "_1", count(ProjectReports[ReportID]), "_2",count(ProjectReports[ReportID])+0), isblank(Title)) it will return only the rows taht don't have teh TItle filled in ProjectReports.

But my issue for when an user doesn't fill a report(a row in ProjectReports) for a month for a  report(If a reprot has the status different from Closed it should have a row for each month in ProjectReports with a ReportID,..)