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.

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.