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 ha...
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.

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?