Count Occurrences of future dates based on single date column for site locations

[C-G-Davidson]

Looking for some help on counting dates in a column which will allow filtering per location.

If we imagine a fictional warehouse table where id is a single order.

Id	Received	Processed	Checked	Dispatched
1	2020-01-01	2020-01-01	2020-01-01	2020-01-01
2	2020-01-01	2020-01-01	2020-01-02	2020-01-03
3	2020-01-02	2020-01-02	2020-01-02	2020-01-10
4	2020-01-02	2020-01-02	2020-01-02	2020-01-02

I have linked the received field to a column of single dates in my calendar table.

What I am looking for is to list out each date from the calendar and count the number of dates for each category that match so e.g. for the date 2020-01-01
We recieved 2 orders

We processed 2 orders

We checked 1 order

We dispatched 1 order

For the table of orders above it would result in

Date	Received	Processed	Checked	Dispatched
2020-01-01	2	2	1	1
2020-01-02	2	2	3	1
2020-01-03	0	0	0	1
2020-01-10	0	0	0	1

I thought I had it with this code where I added a count of dates to the calendar table in a calculated column. One column for each count of received, processed, checked, dispatched.

'checked one below'

checked_orders = CALCULATE(COUNTROWS(orders), FILTER(orders, orders[checked] = 'Calendar'[Date]))

Terribly inneficient I know but I am trying anything. Anyway the method doesn't seem to be the correct way as it won't allow filtering on locations. 

If you imagine the orders table is filled with orders from 10 locations identified by a location_id 1-10 . If the user selects a specific location e.g. location 2 then the results table should only list the count of recieved, processed,checked,dispatched for location 2.

For help, it is safe to assume that every day of the year will have at least 1 order per day.

Sorry if the description seems strange, the actual problem relates to a pharmacy company with many branches so tried to relate it to an easier to explain problem.

[Fowmy · Accepted solution]

@C-G-Davidson 

You need to create four measures and no need to link the date table with the calendar table if you arent not using it for any other purpose.

Received Count = 
COUNTROWS(
    FILTER(
        Table10,
        Table10[Received] = MAX('Table'[Date])
    )
)

Processed Count = 
COUNTROWS(
    FILTER(
        Table10,
        Table10[Processed] = MAX('Table'[Date])
    )
)

Checked Count = 
COUNTROWS(
    FILTER(
        Table10,
        Table10[Checked] = MAX('Table'[Date])
    )
)

Dispatched Count = 
COUNTROWS(
    FILTER(
        Table10,
        Table10[Dispatched] = MAX('Table'[Date])
    )
)

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn