cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

Compare data from snapshot table for two user selected dates

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_Datedemand_idexternal_rack_nameQuantity_Num
5/30/2018a1234A1
5/30/2018a1235A1
5/30/2018a1236 1
5/30/2018a1237B1
5/30/2018a1237B1
5/30/2018a1239C1
5/30/2018a1240D1
5/30/2018a1241D1
5/30/2018a1242D1
5/30/2018a1243E1
6/6/2018a1236 1
6/6/2018a1237B1
6/6/2018a1237B1
6/6/2018a1239C1
6/6/2018a1240D1
6/6/2018a1241D1
6/6/2018a1242D1
6/6/2018a1243E1
6/6/2018a1244F1
6/6/2018a1245A1
6/6/2018a1246A1

 

Thanks!

3 REPLIES 3
Highlighted
Regular Visitor

Re: Compare data from snapshot table for two user selected dates

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!

Highlighted
Regular Visitor

Re: Compare data from snapshot table for two user selected dates

Thoughts or direction from anyone? Really appreciate the help!

 

Thanks.

Highlighted
Community Support
Community Support

Re: Compare data from snapshot table for two user selected dates

Hi @mf720

How do you define the DemandID which is added or deleted?

If the DemandID is deleted after refresh, it seems we can't calculate it.

Additionally, I find an article which seems to be useful to help you.

https://exceleratorbi.com.au/conditionally-refresh-queries-power-bi/

 

Best Regards

Maggie

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors