Forum Discussion
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!
Thoughts or direction from anyone? Really appreciate the help!
Thanks.