Creating a table to count orders using multiple dates and statuses

[Anonymous]

Hey Everyone,

So I have something that has been giving me trouble for over a week now, I have searched all over this forum and cant find the answer. Could use your expertise on this one.

I have a large data set that has rows that look like the following:

Name    I   order status   I   requested quantity   I   country   I   confirmation date  I  estimated ship date   I   actual delivery date

Requested quantity (the number of items per order),

Confirmation date (when they placed the order),

Estimated ship date (when we think they will recieve it),

Actual delivery date (when they actually recieve it).

I need to create a table which can calculate the requested quantity per country and per date based on its status.

For example, if the order status is confirmed, we should sum the requested quantity by date using estimated ship date. If the order status is delivered, we use actual delivery date etc.

The end result should look like this.

Would be forever greatful if someone could help me out on this.

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

Create new tables

cross table =
ADDCOLUMNS (
    CROSSJOIN (
        VALUES ( sampledata[ROStatus] ),
        VALUES ( sampledata[ShipToCountry] )
    ),
    "merge", [ROStatus] & "-" & [ShipToCountry]
)


date = ADDCOLUMNS(CALENDARAUTO(),"year",YEAR([Date]),"month",MONTH([Date]))

Create columns in "sampledata" table

date2 =
SWITCH (
    [ROStatus],
    "Confirmed Purchase Order", [EstimatedShipDate],
    "Delivery", [ActualDeliveryDate],
    [ConfirmationDate]
)


merge = [ROStatus]&"-"&[ShipToCountry]

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-juanli-msft]

Hi @Anonymous 

Please try suggestions from jairoaol, if problem is still here, please feel free to ask further.

Best Regards

Maggie

[jairoaol]

send test data to validate.

Based on what you expose, you need a measure that counts the records in the table.

you then create a table or matrix that combines the states, dates, and measure created.

[Anonymous]

Thanks for the quick response

I have attached sample data here. 

In regards to counting the records, the issue is I would need to count the requested quantity no?