topic Re: Cumulative count with date filters - got stuck and desperate in DAX Commands and Tips

Cumulative count with date filters - got stuck and desperate

Anonymous — Sat, 15 Oct 2022 15:04:22 GMT

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.

Re: Cumulative count with date filters - got stuck and desperate

tamerj1 — Sat, 15 Oct 2022 16:51:13 GMT

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

Please try

Count = SUMX ( Table1, VAR DateCreated = Table1[Dasboard Created] VAR LastAdded = CALCULATE ( MAX ( Table2[Part # was added on] ) ) VAR PartsAdded = COUNTROWS ( CALCULATETABLE ( Table2 ) ) RETURN IF ( PartsAdded > 0 && LastAdded <= DateCreated, 1 ) )

Re: Cumulative count with date filters - got stuck and desperate

Anonymous — Sat, 15 Oct 2022 18:14:32 GMT

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?

Re: Cumulative count with date filters - got stuck and desperate

tamerj1 — Sat, 15 Oct 2022 20:27:47 GMT

Anonymous

yes. The date when dashboard was created should work.

Re: Cumulative count with date filters - got stuck and desperate

Anonymous — Sat, 15 Oct 2022 20:37:16 GMT

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

Re: Cumulative count with date filters - got stuck and desperate

tamerj1 — Sun, 16 Oct 2022 06:58:45 GMT

Anonymous

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.

Re: Cumulative count with date filters - got stuck and desperate

Anonymous — Sun, 16 Oct 2022 09:46:20 GMT

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)

😎

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.

Re: Cumulative count with date filters - got stuck and desperate

tamerj1 — Sun, 16 Oct 2022 12:13:37 GMT

Anonymous

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?

Re: Cumulative count with date filters - got stuck and desperate

Anonymous — Sun, 16 Oct 2022 12:27:59 GMT

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:

Re: Cumulative count with date filters - got stuck and desperate

tamerj1 — Sun, 16 Oct 2022 12:34:42 GMT

Hi Anonymous

please try

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, VAR LastAdded = CALCULATE ( MAX ( Table2[Part # was added on] ) ) VAR PartsAdded = COUNTROWS ( CALCULATETABLE ( Table2 ) ) RETURN IF ( PartsAdded > 0 && LastAdded <= DateCreated, 1 ) )

Re: Cumulative count with date filters - got stuck and desperate

Anonymous — Mon, 17 Oct 2022 09:21:36 GMT

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.

Re: Cumulative count with date filters - got stuck and desperate

tamerj1 — Sun, 16 Oct 2022 13:10:11 GMT

Anonymous

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

Re: Cumulative count with date filters - got stuck and desperate

Anonymous — Mon, 17 Oct 2022 09:22:09 GMT

Unfortunately, I can't share data model screenshots.

Re: Cumulative count with date filters - got stuck and desperate

tamerj1 — Sun, 16 Oct 2022 13:19:14 GMT

Anonymous

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, VAR LastAdded = CALCULATE ( MAX ( Table2[Part # was added on] ), ALL ( Table1[Dasboard Created] ) ) VAR PartsAdded = COUNTROWS ( CALCULATETABLE ( Table2, ALL ( Table1[Dasboard Created] ) ) ) RETURN IF ( PartsAdded > 0 && LastAdded <= DateCreated, 1 ) )

Re: Cumulative count with date filters - got stuck and desperate

Anonymous — Fri, 28 Oct 2022 21:46:15 GMT

Re: Cumulative count with date filters - got stuck and desperate

tamerj1 — Sun, 16 Oct 2022 13:47:25 GMT

Ok please share the meeting link

Re: Cumulative count with date filters - got stuck and desperate

tamerj1 — Mon, 17 Oct 2022 13:14:35 GMT

Hi Anonymous
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 ), Table2[Added On] <> BLANK () ) VAR PartsAdded1 = COUNTROWS ( Parts1 ) VAR LastAdded1 = MINX ( Parts1, Table2[Added On] ) RETURN IF ( PartsAdded1 > 0 && LastAdded1 <= CurrentDate, 1 ) ) VAR pj_type2 = SUMX ( AllProjects, VAR Parts2 = FILTER ( CALCULATETABLE ( Table2, USERELATIONSHIP ( Table3[Code], Table4[Code] ), Table3[Date created] <= current_date ), Table2[Added On] <> BLANK () ) VAR PartsAdded = COUNTROWS ( Parts2 ) VAR LastAdded = MINX ( Parts2, Table2[Added On] ) RETURN IF ( PartsAdded2 > 0 && LastAdded2 <= CurrentDate, 1 ) ) RETURN pj_type1 + pj_type2