Chronological view (by date) of items in queue

[Theriaux]

Hello,

I want to visualize a count of (x) by day based on (y), which represents several date cells in a spreadsheet.

The date cells are as follows: (Last Entry), (Review), and (Received).

There are multiple outcomes for (y):

y   = (Last Entry) is blank, (Review) is blank, (Received) is blank.

y1 = (Last Entry) has a date entered, (Review) is blank, (Received) is blank.

y2 = (Last Entry) has a date entered, (Review) has a date entered, (Received) is blank.

y3 = (Last Entry) is blank, (Review) has a date entered, (Received) is blank.

y4 = (Received) has a date entered and all other date cell conditions do not matter.

Equation elements

x = count of documents

y (is variable) = examples shown above 

I need a calculated field or formula that will allow me to count (x) under the following conditions and NOT COUNT (x) under a different set of conditions.

Count (x) when (y) = y1, y2, and y3

DO NOT count (x) when (y) = y, y4

How do i visualize this based on dates and how would i know the best Date Field (Last Entry), (Reviewed), and (Received) to use as my X-Axis?

Thanks.

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[Theriaux]

I cannot find a way to attache my Excel sheet. Here are images..

[v-yiruan-msft · Accepted solution]

Hi @Theriaux ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want. You can create two measures as below:

Parts Dept = 
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Part] ),
    FILTER (
        'Table',
        NOT ( ISBLANK ( 'Table'[Issued] ) )
            && ( NOT ( ISBLANK ( 'Table'[Entry] ) ) || NOT ( ISBLANK ( 'Table'[Reviewed] ) ) )
            && ISBLANK ( 'Table'[Received] )
    )
)

Receiving Dept =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Part] ),
    FILTER ( 'Table', NOT ( ISBLANK ( 'Table'[Received] ) ) )
)

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards