Count of Items by date

[Purpleshimmo]

Hi Guys

I am new to Power BI and been trying to figure out how to solve the following problem.  We receive requests for information (RFIs), each one has a unique title, is received on a specific date and is responded to on a specific date.  I want to create a visual that plots the count of titles received and count of titles responded to by date so I can see if we are falling behind on responding to RFIs on day, week or monthly drill down.  A sample data table is attached.  I have tried to create measures, but I am very much at the start of learning to use Power BI, thanks.

Paul

https://arup-my.sharepoint.com/:x:/p/paul_simmonite/Edsfs0UrvpJBpBaS7xIU1CwBJkJMgZMPlYt8pZoy0lP1Rg?e... 

[v-shex-msft · Accepted solution]

Hi @Purpleshimmo ,

You can add a calendar table and write two measure to calculate correspond title count based on current calendar date and receive/response date:

Received =
CALCULATE (
    COUNT ( 'Table'[Title] ),
    FILTER ( ALLSELECTED ( 'Table' ), [Date Received] <= MAX ( 'Calendar'[Date] ) )
)

Responded =
CALCULATE (
    COUNT ( 'Table'[Title] ),
    FILTER ( ALLSELECTED ( 'Table' ), [Date Responded] <= MAX ( 'Calendar'[Date] ) )
)

Notice: The calendar table does not have a relationship to original table date fields.

Regards,

Xiaoxin Sheng

[Purpleshimmo]

Hi @v-shex-msft 

I've noticed that because there are blank dates in the responded to column, the measure is summing them as if they are dates.  For example, the data table shows no RFIs are responded to until 6 October 2019, when 3 are recorded.  The [Responded] measure in the visualization shows 6 responded to on 6 October, the 3 blank cells and the 3 actual dates.    So the blank cells are messing it up.  There will always be blank cells until an RFI is responded to.  Any suggestions how to deal with this?

Many thanks for your help.

Paul

[Purpleshimmo]

I've found a workaround by creating a column measure that looks for null cells and replaces them with a date in one year from now.  Its not pretty, but it works for now.  

Thanks.

Paul