cancel
Showing results for
Did you mean:
Helper III

## Cumulative count with date filters - got stuck and desperate

Hey guys, so, long story short:
I need a bar chart that will count the number of projects over time with several conditions. Below are the tables that are needed (for the sake of simplicity let's focus on project name 976):
1) Table 1

We can see the project's name and the date its dashboard was created

2) Table 2

We have a table with the project's name and the date when a part number for this project was added.

Now, for the main part:

we can see that the dashboard for project 976 was created in August 2022

we can see that parts were added to this project, both before AND in the same month the dashboard was created

Goal - to calculate the cumulative number of projects (starting from the earliest date the dashboard was created) from table 1, BUT:
• There should be AT LEAST ONE part # that was added BEFORE or IN THE SAME MONTH the dashboard for the project was created. - So, the bar chart will count project 976 - because it has both dashboard created in August and at least one part that was added before or in the same month.
• Now, let's imagine that project 976 had its dashboard created in August 2022, but a part # was firstly added in September 2022:
• That means that project 976 should not be counted for August 2022, because part # was added in the next month. 976 would be counted on the chart starting September 2022.

• If a project has no parts added (BLANK), obviously, it should not be counted at all.

I would be eternally grateful to the person who will help me with it  + a little reward from me, personally.

1 ACCEPTED SOLUTION
Super User

Hi @PreacherBaby
Final Solution is as follows

``````Count =
VAR CurrentDate =
MAX ( 'Date'[Date] )
VAR AllProjects =
FILTER ( Table1, Table1[Date created] <= current_date )
RETURN
VAR pj_type1 =
SUMX (
AllProjects,
VAR Parts1 =
FILTER (
CALCULATETABLE ( Table2, Table3[Date created] <= current_date ),
)
COUNTROWS ( Parts1 )
MINX ( Parts1, Table2[Added On] )
RETURN
)
VAR pj_type2 =
SUMX (
AllProjects,
VAR Parts2 =
FILTER (
CALCULATETABLE (
Table2,
USERELATIONSHIP ( Table3[Code], Table4[Code] ),
Table3[Date created] <= current_date
),
)
COUNTROWS ( Parts2 )
MINX ( Parts2, Table2[Added On] )
RETURN
)
RETURN
pj_type1 + pj_type2``````

16 REPLIES 16
Super User

Hi @PreacherBaby
Final Solution is as follows

``````Count =
VAR CurrentDate =
MAX ( 'Date'[Date] )
VAR AllProjects =
FILTER ( Table1, Table1[Date created] <= current_date )
RETURN
VAR pj_type1 =
SUMX (
AllProjects,
VAR Parts1 =
FILTER (
CALCULATETABLE ( Table2, Table3[Date created] <= current_date ),
)
COUNTROWS ( Parts1 )
MINX ( Parts1, Table2[Added On] )
RETURN
)
VAR pj_type2 =
SUMX (
AllProjects,
VAR Parts2 =
FILTER (
CALCULATETABLE (
Table2,
USERELATIONSHIP ( Table3[Code], Table4[Code] ),
Table3[Date created] <= current_date
),
)
COUNTROWS ( Parts2 )
MINX ( Parts2, Table2[Added On] )
RETURN
)
RETURN
pj_type1 + pj_type2``````

Super User

Hi @PreacherBaby
There is no information given about the relationships between the two tables therefore, I assume that Table1 filters Table2 (One to Many) relationship.

``````Count =
SUMX (
Table1,
VAR DateCreated = Table1[Dasboard Created]
CALCULATE ( MAX ( Table2[Part # was added on] ) )
COUNTROWS ( CALCULATETABLE ( Table2 ) )
RETURN
)``````
Helper III

Yes, table 1 filters table 2. The measure works but does not return correct values just yet, I suspect it's because there are incorrect values in x axis date values in the chart

What date data should I put into the bar chart's X axis? Should it be the date when the dashboard was created?

Super User

yes. The date when dashboard was created should work.

Helper III

Unfortunately, it didn't work:( Maybe it's because of SUMX?

Because I need to count every distinct project that has part # added before or during the month its dashboard was created

Super User

As per the sample data you have provided each row in Table1 represents one project. If this is not correct please let me know. The SUMX is just to have a correct result at the total level.

Helper III

So, based on your expression it returns the count of 12 for the month of August:

After manually going through the tables, it is known that there should be 18 projects counted for the month of August, here is the list of screenshots for every project (for understanding):
1)

As you can see, project 1009 had its part added before or during the month the dashboard was created, so it should be counted for the month of August and the upcoming months (like cumulative)

2)

3)

4)

5)

6)

7)

😎

9)

10)

11)

12)

13)

14)

15)

16)

17)

18)

So, here they are - 18 projects that meet conditions and should be counted for August and so on (as a cumulative count).

BUT:

We have a project in which the dashboard was added in August, but parts # were added in September:

So, in August we have 18 projects, but in September we should have 19, this new project's parts were added in the next month - that's why we start counting it from September.

Super User

One question, if you change the data type format to DD/MM/YYYY what DD to you get? Is there a difference between Dashboard Added Date and the Part Added Date?

Helper III

Yes, there are differences. Both dashboards added date and part # added dates are datetimes, but since my granularity is month, I formatted them:
This is how they look without month formatting:

Super User

``````Count =
VAR DateCreated =
EOMONTH ( SELECTEDVALUE ( Table1[Dasboard Created] ), 0 )
VAR ProjectsOnAndBefore =
CALCULATETABLE (
VALUES ( Table1[Projet Name] ),
ALL ( Table1[Dasboard Created] ),
Table1[Dasboard Created] <= DateCreated
)
RETURN
SUMX (
ProjectsOnAndBefore,
CALCULATE ( MAX ( Table2[Part # was added on] ) )
COUNTROWS ( CALCULATETABLE ( Table2 ) )
RETURN
)``````
Helper III

It has counted 9 instead of 18 for August and didn't do cumulative count for subsequent months, i.e., the same project count wasn't returned for September or October. And it counted the project which has no part # added for some reason.

Super User

Would you please provide a screenshot of your data modle? Do you have a date table?

Helper III

Unfortunately, I can't share data model screenshots.

Super User

I hope the relationship is based on the project name column?
please try the following otherwise, let me know if we can connect via teams or zoom

``````Count =
VAR DateCreated =
EOMONTH ( SELECTEDVALUE ( Table1[Dasboard Created] ), 0 )
VAR ProjectsOnAndBefore =
CALCULATETABLE (
VALUES ( Table1[Projet Name] ),
ALL ( Table1[Dasboard Created] ),
Table1[Dasboard Created] <= DateCreated
)
RETURN
SUMX (
ProjectsOnAndBefore,
CALCULATE (
MAX ( Table2[Part # was added on] ),
ALL ( Table1[Dasboard Created] )
)
COUNTROWS ( CALCULATETABLE ( Table2, ALL ( Table1[Dasboard Created] ) ) )
RETURN
)``````

Helper III

//

Super User

Announcements

#### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

#### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

#### 2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors