How to calculate # of active projects with status on each day.

[BIWConsult]

Hi, 

I'm currently working on a project where we want to plot the number of projects in a given time frame and see how many of them have which status on each day. 

So lets say we have:

Project ID	Status	Date
Project A	Pending	01/01/2022
Project A	Accepted	04/01/2022
Project A	Executed	05/01/2022
Project B	Pending	03/01/2022
Project B	Accepted	04/01/2022
Project C	Pending	04/01/2022

What we want to do is track how many projects have a certain status every day.

Like this:

Date	# of Pending	# of Accepted	# of Executed
01/01/2022	1 (A)	0	0
02/01/2022	1 (A)	0	0
03/01/2022	2 (A+B)	0	0
04/01/2022	1 (C)	2 (A+B)	0
05/01/2022	1 (C)	1 (B)	1 (A)

Any ideas on how we could achieve this?

This is how we currently tried to calculate this, the problem is that the cumulative keeps going, even if the status of a project changes.

[tamerj1]

Hi @BIWConsult 
Actually I had a 2nd look at your request and I thought there is another option to do this which is using a mtrix visual instead of a table. You can have a disconnected table containing all unique status value and use it in the columns of the matix along with the only one measure (instead of three measures) as follows

Count = 
VAR CurrentDate = 
    MAX ( 'Date'[Date] )
VAR CurrentStatus =
    SELECTEDVALUE ( 'Status'[Status] )
VAR CurrentProjects = 
    CALCULATETABLE ( 
        VALUES ( Data[Project ID] ), 
        ALL ( 'Date' ),
        'Date'[Date] <= CurrentDate 
    )
VAR SelectedProjects =
    FILTER ( 
        CurrentProjects,
        VAR CurrentData = CALCULATETABLE ( Data, ALL ( 'Date' ), 'Date'[Date] <= CurrentDate )
        VAR LastRecord = TOPN ( 1, CurrentData, Data[Date] )
        VAR LastStatus = MAXX ( LastRecord, Data[Status] )
        RETURN
            LastStatus = CurrentStatus
    )
RETURN
    COUNTROWS ( SelectedProjects )

[CNENFRNL · Accepted solution]

[tamerj1]

Hi @BIWConsult 

Please refer to attached sample file.

I assume you have an active relationship between your projects table and the date table which you don't have any intention to remove it.

The "CurrentProjects" is just an optimization to reduce the number of unnecessary iterations.

# of Accepted = 
VAR CurrentDate = 
    MAX ( 'Date'[Date] )
VAR CurrentProjects = 
    CALCULATETABLE ( 
        VALUES ( Data[Project ID] ), 
        ALL ( 'Date' ),
        'Date'[Date] <= CurrentDate 
    )
VAR AcceptedProjects =
    FILTER ( 
        CurrentProjects,
        VAR CurrentData = CALCULATETABLE ( Data, ALL ( 'Date' ), 'Data'[Date] <= CurrentDate )
        VAR LastRecord = TOPN( 1, CurrentData, Data[Date] )
        VAR LastStatus = MAXX ( LastRecord, Data[Status] )
        RETURN
            LastStatus = "Accepted"
    )
RETURN
    COUNTROWS ( AcceptedProjects )

[Greg_Deckler]

@BIWConsult So why not create a Matrix visual with Date as rows, Status as Columns and a distinct count of Project ID as values?

[BIWConsult]

Hi Greg,

The problem is the client wants it in a column chart like this, in order to make the evolution more visible.

Kind regards