How to list products having orders on the selected day but not having any on the previous day?

[stormzh]

Hi friends,

I am new to this community. I have a question in PowerBI Desktop that I hope you could give me some advice.

I have a simple table for my product orders for each day and it has columns like the following:

Channel       Product ID     Order Amount      Order Date

website         PID-001            4                           Jan 6, 2017

mobileapp    PID-001            5                           Jan 6, 2017

onsite            PID-003           12                         Jan 6, 2017

onsite            PID-002           8                           Jan 6, 2017

mobileapp    PID-002            2                          Jan 6, 2017

mobileapp    PID-002           15                          Jan 5, 2017

website         PID-004           20                          Jan 5, 2017

mobileapp    PID-004           11                          Jan 5, 2017

I have a date filter added to the report and it allows me to choose any single date. I am wondering how I can display a list of all the product IDs that have orders on the selected day but do not have any orders on the previous day. And vice versa. In addition to the product ID, I also want to include the sum of order amount for each of the product IDs displayed in the lists.

For the example data source listed above, if I select Jan 6, 2017 from the date filter, what I want is two lists like the following:

-- List #1 shows the products IDs having order on the selected day (Jan 6, 2017) but not having any order on the previous day (Jan 5, 2017)

Product ID     SUM of Order Amount   

PID-001           9

PID-003           12

-- List #2 shows the products IDs having order on the previous day (Jan 5, 2017) but not having any orders on the selected day (Jan 6, 2017)

Product ID     SUM of Order Amount   

PID-004           31

Thanks in advance for any help from you!

Regards,

stormzh

[Vvelarde · Accepted solution]

@stormzh

Hi,

1. Create a new Table (Modeling - New Table)

ProductIds = DISTINCT(Table1[  ProductID   ])

2. Create 2 measures:

OrderAmountM =
IF (
    HASONEVALUE ( ProductIds[  ProductID   ] );
    CALCULATE (
        SUM ( Table1[OrderAmount  ] );
        FILTER (
            Table1;
            Table1[  ProductID   ] = VALUES ( ProductIds[  ProductID   ] )
        )
    );
    SUMX (
        SUMMARIZE (
            ProductIds;
            ProductIds[  ProductID   ];
            "ORDERS"; CALCULATE (
                SUM ( Table1[OrderAmount  ] );
                FILTER (
                    Table1;
                    Table1[  ProductID   ] = VALUES ( ProductIds[  ProductID   ] )
                )
            )
        );
        [ORDERS]
    )
)

OrderinPreviousDay =
VAR orderDatePrev =
    MAX ( Table1[ OrderDate] ) - 1
RETURN
    IF (
        HASONEVALUE ( ProductIds[  ProductID   ] );
        CALCULATE ( [OrderAmountM]; DATEADD ( Table1[ OrderDate]; -1; DAY ) );
        SUMX (
            SUMMARIZE (
                ProductIds;
                ProductIds[  ProductID   ];
                "ORDERS"; CALCULATE ( [OrderAmountM]; DATEADD ( Table1[ OrderDate]; -1; DAY ) )
            );
            [ORDERS]
        )
    )

3. Use a Slicer to Date Order

4. Create a Table to List 1

5. Create a Table to List2

[stormzh]

Hello again, @Vvelarde since you did so well in helping me with this specific issue, I am wondering if you could kindly look into another issue I am having. I put the detailed scenario I want to achieve and also some sample data records at http://community.powerbi.com/t5/Desktop/How-to-calculate-weekly-sales-amount-ranking-difference/m-p/...

Thanks a lot in advance for any idea or feedback you could share.

Best Regards,

stormzh 

[stormzh]

That did the trick! Thank you so much for the help, Vvelarde !