cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PreacherBaby
Helper III
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
 1.png

 

We can see the project's name and the date its dashboard was created
 
2) Table 2
2.png

 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:
3.png

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

 

4.pngwe 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
tamerj1
Super User
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 ),
                    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

 

View solution in original post

16 REPLIES 16
tamerj1
Super User
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 ),
                    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

 

tamerj1
Super User
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.

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 )
)

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?

@PreacherBaby 

yes. The date when dashboard was created should work. 

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

@PreacherBaby 

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. 

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

PreacherBaby_0-1665911968183.png

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) 

PreacherBaby_1-1665912100536.png

 

PreacherBaby_2-1665912138503.png

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)

PreacherBaby_3-1665912237880.png

PreacherBaby_4-1665912257665.png

3)

PreacherBaby_5-1665912298093.pngPreacherBaby_6-1665912315428.png

4)

PreacherBaby_7-1665912349707.png

PreacherBaby_8-1665912361967.png

 

 

5)

PreacherBaby_9-1665912522759.pngPreacherBaby_10-1665912539294.png

6)

PreacherBaby_11-1665912567619.pngPreacherBaby_12-1665912585372.png

7)

PreacherBaby_13-1665912602328.pngPreacherBaby_14-1665912614759.png

😎 

PreacherBaby_15-1665912638888.pngPreacherBaby_16-1665912652556.png

9) 

PreacherBaby_17-1665912681584.pngPreacherBaby_18-1665912689187.png

10) 

PreacherBaby_19-1665912716828.pngPreacherBaby_20-1665912728267.png

11)

PreacherBaby_21-1665912747015.pngPreacherBaby_22-1665912754873.png

12)

PreacherBaby_23-1665912771953.pngPreacherBaby_24-1665912806717.png

13)

PreacherBaby_25-1665912826061.pngPreacherBaby_26-1665912842456.png

14)

PreacherBaby_27-1665912857044.pngPreacherBaby_28-1665912867507.png

15)

PreacherBaby_29-1665912896131.pngPreacherBaby_30-1665912907126.png

16) 

PreacherBaby_32-1665912969876.png

PreacherBaby_33-1665913011389.png

17) 

PreacherBaby_34-1665913048327.pngPreacherBaby_35-1665913055724.png

18) 

PreacherBaby_36-1665913079596.png

PreacherBaby_37-1665913099098.png


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:

PreacherBaby_38-1665913290184.png

PreacherBaby_39-1665913318050.png

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. 

 

 

 

@PreacherBaby 

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?

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:

PreacherBaby_1-1665923108478.png

PreacherBaby_2-1665923212687.png

 

 

Hi @PreacherBaby 

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 )
    )

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. 

@PreacherBaby 

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

Unfortunately, I can't share data model screenshots.

@PreacherBaby 

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 )
    )

 

//

Ok please share the meeting link

Helpful resources

Announcements
Carousel_PBI_Wave1

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.

Power BI Summit Carousel 2

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!

Thank you 2022 Review

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