Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Creating a table to count orders using multiple dates and statuses

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.

 

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    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.

4 Replies

  • jairoaol's avatar
    jairoaol
    Impactful Individual
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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?


  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    6 years ago

    Hi Anonymous 

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

     

    Best Regards

    Maggie

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    6 years ago

    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.