Compare data from snapshot table for two user sele...

mf720 · ‎06-18-2018

Hi all,

I have a problem I've been trying to solve in Power BI desktop for a few days with no luck. Here is a simplified explanation and dataset that shows what I'm trying to do. I have a table Orders that stores a snapshot of current orders captured daily for a 2 week period. New orders can be added each day, and orders can also be deleted (if deleted, the order record is removed from that snapshot completely). The Order file is obtained externally, so the logic can't be adjusted.

Desired outcome: I'd like to allow the user to select one snapshot date, and have some visuals populate that shows basic details on the orders from that snapshot date ("Report_Date" column). And also allow the user to select a second date and have a measure show the new orders that were added in the later snapshot, and a measure show the orders that were deleted from the earlier snapshot (which means the order's "demand_id" exists in the first snapshot but not in the second). This is the part I'm struggling with. Note that I'd like the "NewOrders" and "DeletedOrders" measures to respond to other filters selected by the user, for example filter by product type (called external_rack_name) in the test dataset.

See below for a sample dataset. In this dataset, the expected output from a comparison of Report_Date 6/6 and 5/30 is 3 new orders (new demand_id values in the snapshot) and 2 deleted orders (demand_id values removed from the snapshot).

I will add a reply to this thread with details on my attempted logic/formulas so far, in case that speeds up the process of assisting. Thank you for any assistance or direction that you can provide!

Sample dataset:

Report_Date	demand_id	external_rack_name	Quantity_Num
5/30/2018	a1234	A	1
5/30/2018	a1235	A	1
5/30/2018	a1236		1
5/30/2018	a1237	B	1
5/30/2018	a1237	B	1
5/30/2018	a1239	C	1
5/30/2018	a1240	D	1
5/30/2018	a1241	D	1
5/30/2018	a1242	D	1
5/30/2018	a1243	E	1
6/6/2018	a1236		1
6/6/2018	a1237	B	1
6/6/2018	a1237	B	1
6/6/2018	a1239	C	1
6/6/2018	a1240	D	1
6/6/2018	a1241	D	1
6/6/2018	a1242	D	1
6/6/2018	a1243	E	1
6/6/2018	a1244	F	1
6/6/2018	a1245	A	1
6/6/2018	a1246	A	1

Thanks!

mf720 · ‎06-18-2018

Here are some details on my attempted logic so far. Although I'm sure there are better approaches (this shows how new I am to DAX and Power BI).

First I created two calculated tables: Compare_Date1 and Compare_Date2, to use for date slicers for the user to select snapshot dates for comparison. Here's the DAX for both tables (which just creates a single unique column with all Report_Date values):

= ALLNOBLANKROW(Orders[Report_Date])

I created a relationship between Compare_Date1 and the main Orders table, but no relationship for Compare_Date2. This was to allow the Compare_Date1 selection to impact all visuals on the report, but to ensure the Compare_Date2 filter is just selectively used in the comparison measures.

Here is my DAX measure to calculate new orders (FYI I edited the original post to properly format the DAX):

----------

NewDemandID =
CALCULATE (
    COUNTROWS (
        CALCULATETABLE (
            Orders,
            ALL ( Orders[Report_Date] ),
            FILTER (
                Orders,
                Orders[Report_Date] = SELECTEDVALUE ( Compare_Date1[Report_Date] )
            ),
            EXCEPT (
                VALUES ( Orders[demand_id] ),
                SELECTCOLUMNS (
                    FILTER (
                        ALL ( Orders[Report_Date], Orders[demand_id] ),
                        Orders[Report_Date] = SELECTEDVALUE ( Compare_Date2[Report_Date] )
                    ),
                    "demand_id", Orders[demand_id]
                )
            )
        )
    )
)

----------

The NewDemandID measure seems to work, as it returns 3, and also responds to additional filters if the I add/filter on anything else.

However, my attempt for the DeletedDemandID isn't working (FYI I edited the original post to properly format the DAX):

----------

RemovedDemandID =
CALCULATE (
    COUNTROWS ( Orders ),
    EXCEPT (
        CALCULATETABLE (
            VALUES ( Orders[demand_id] ),
            FILTER (
                ALL ( Orders[Report_Date] ),
                Orders[Report_Date] = SELECTEDVALUE ( Compare_Date2[Report_Date] )
            )
        ),
        CALCULATETABLE (
            VALUES ( Orders[demand_id] ),
            FILTER (
                ALL ( Orders[Report_Date] ),
                Orders[Report_Date] = SELECTEDVALUE ( Compare_Date1[Report_Date] )
            )
        )
    ),
    ALL ( Orders[Report_Date] )
)

----------

Note that on the table visual I added to show the DeletedDemandID measure results, I changed the interactions (for the Compare_Date1 selection) from Filter to None... since the deleted demand_id values will only show in the second snapshot date records, not the first, I didn't want the visual to filter those out from the start, since the whole point is to show the removed orders.

The DeletedDemandID measure returns zero. It feels like I didn't adjust the filter context correctly in the DAX, but I haven't found the right combination. Interestingly enough, if I hardcode the selected dates (instead of using SELECTEDVALUE to grab the user selections) it seems to work.

Any thoughts or direction would be greatly appreciated. I'm also very open to a completely new method if this one is too inefficient, or not the right way to approach this problem.

Thank you!